CODEWITHSUNDEEP
sql-servertriggers
sAaNu
sAaNu

Reputation: 1508

Help on SQL Server trigger

Suppose I have 3 tables

t1

Nid   name
1    aaa
2    bbb
3    ccc

delT1

Nid    name

t2

Sid    Nid     value
1      1       AAA
2      1       BAC
3      2       CSA

In table t1 Nid is primary key which is foreign key for t2

Now what I want is when I delete value from t1 it automatically deletes all values from t2 where t1.Nid=t2.Nid and a value of deleted t1 get inserted into delT1

How can I create a trigger for this type of task?

Please help me since I am new to sql

Upvotes: 2

Views: 344

Answers (3)

ErikE
ErikE

Reputation: 50271

Modify the FK in T2 to be ON DELETE CASCADE:

ALTER TABLE T2 DROP CONSTRAINT FK_T1_Nid; <-- your constraint name here
ALTER TABLE T2 ADD CONSTRAINT FK_T1_Nid FOREIGN KEY (Nid)
   REFERENCES T1 (Nid) ON DELETE CASCADE;

Then create a trigger on T1 to push the information to delT1:

CREATE TRIGGER TR_T1_D ON T1 FOR DELETE
AS
SET NOCOUNT ON;
INSERT delT1
SELECT Nid, Name
FROM Deleted;

Note this trigger prevents you from using an OUTPUT clause on DELETEs against T1. BOL says:

If the OUTPUT clause is specified without also specifying the INTO keyword, the target of the DML operation cannot have any enabled trigger defined on it for the given DML action. For example, if the OUTPUT clause is defined in an UPDATE statement, the target table cannot have any enabled UPDATE triggers.

Upvotes: 2

Yuck
Yuck

Reputation: 50855

@gbn has the preferred way to go. Since you asked for a trigger, you could do this for comparison's sake:

CREATE TRIGGER t1_Delete ON t1
INSTEAD OF DELETE AS BEGIN SET NOCOUNT ON;
  INSERT INTO delT1 (Nid, name)
  SELECT Nid, name
  FROM DELETED;

  DELETE FROM t2
  WHERE t2.Nid IN (SELECT Nid FROM DELETED);

  DELETE FROM t1
  WHERE t1.Nid IN (SELECT Nid FROM DELETED);
END;

Upvotes: 2

gbn
gbn

Reputation: 432561

A normal trigger wouldn't work: the foreign key would give an error before the code runs.

What you can do is set a CASCADE on your foreign key so a delete in T1 will delete from T2 automatically.

Personally, I'd use a stored proc and transaction to delete from T2 first, then T1.

Upvotes: 4

Related Questions