Prev: Backup Analysis databases
Next: SQL Server Edition
From: roberta on 18 May 2010 17:03
I've 2 tables:


CREATE TABLE [dbo].[DM](
[ID] [int] IDENTITY(1,1) NOT NULL,
[IDDi] [int] NULL,
[IDMa] [int] NULL,
[Activated] [bit] NULL,
[ConcurrencyId] [timestamp] NULL,
CONSTRAINT [PK_DM] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[DA](
[ID] [int] IDENTITY(1,1) NOT NULL,
[IDDi] [int] NULL,
[IDMa] [int] NULL,
[IDAt] [int] NULL,
[Activated] [bit] NULL,
[ConcurrencyId] [timestamp] NULL,
CONSTRAINT [PK_DA] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


each row of DM can have X reocords of DA

I would like to create a trigger for do this:

if DM.Activated = true

then for ID.Di = DA.IDDi AND DM.IDMa = DA.IDMa

SET Activated = true for all records



if DM.Activated = false

then for DM.IDDi = DA.IDDi AND DM.IDMa = DA.IDMa

SET Activated = false for all records



This for all action in DM (insert/update)

but If I delete a record in DM (new trigger I suppose)

I've to delete all records in DA with

ID.Di = DA.IDDi AND DM.IDMa = DA.IDMa



Thanks for help me!!

From: Erland Sommarskog on 18 May 2010 17:56
roberta (roby(a)tis.it) writes:
> CREATE TABLE [dbo].[DM](
> [ID] [int] IDENTITY(1,1) NOT NULL,
> [IDDi] [int] NULL,
> [IDMa] [int] NULL,
> [Activated] [bit] NULL,
> [ConcurrencyId] [timestamp] NULL,
> CONSTRAINT [PK_DM] PRIMARY KEY CLUSTERED
> (
> [ID] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]
>
>
>
> CREATE TABLE [dbo].[DA](
> [ID] [int] IDENTITY(1,1) NOT NULL,
> [IDDi] [int] NULL,
> [IDMa] [int] NULL,
> [IDAt] [int] NULL,
> [Activated] [bit] NULL,
> [ConcurrencyId] [timestamp] NULL,
> CONSTRAINT [PK_DA] PRIMARY KEY CLUSTERED
> (
> [ID] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]
>
>
> each row of DM can have X reocords of DA

Then there should be a foreign key constraint on DA:

ALTER TABLE DA ADD
CONSTRAINT fk_DA_DM FOREIGN KEY (IDDi, IDMa)
REFERENCES DM (IDDi, IDMa) ON DELETE CASCADE


This also takes care of your second requirement:

> but If I delete a record in DM (new trigger I suppose)
> I've to delete all records in DA with
> ID.Di = DA.IDDi AND DM.IDMa = DA.IDMa


> I would like to create a trigger for do this:
> if DM.Activated = true
> then for ID.Di = DA.IDDi AND DM.IDMa = DA.IDMa
> SET Activated = true for all records
>
>
>
> if DM.Activated = false
> then for DM.IDDi = DA.IDDi AND DM.IDMa = DA.IDMa
> SET Activated = false for all records

CREATE TRIGGER DM_tri ON DM FOR UPDATE AS

UPDATE DA
SET Activated = i.Activated
FROM inserted i
JOIN DA ON i.IDDi = DA.IDDi
AND i.IDMa = DA.IDMa

"inserted" is a virtual table that holds rows that were inserted, or
in case of an UPDATE statement, the after-images of the updated rows.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Backup Analysis databases
Next: SQL Server Edition