CODEWITHSUNDEEP
sql-server-2005
Thomas
Thomas

Reputation: 34188

Instead of trigger before delete

suppose i have one instead of trigger for a table before delete. so i just want to know if i issue a delete then Instead of trigger will fire before delete ?

also tell me how to get deleted data from Instead of trigger before delete. can anyone show me with sample code.

Upvotes: 2

Views: 814

Answers (1)

Martin Smith
Martin Smith

Reputation: 452977

It fires instead of the delete. If you don't implement it yourself nothing gets deleted. These triggers are usually used on Views not Tables.

CREATE TABLE T(
C INT IDENTITY(1,1) PRIMARY KEY,
D INT)

INSERT INTO T (D)
SELECT 1 UNION ALL SELECT 2

GO

CREATE TRIGGER tr ON T
INSTEAD OF DELETE 
AS
BEGIN 
PRINT 'Do Nothing'
END

GO

DELETE 
FROM T

SELECT * 
FROM T     /*The 2 rows are still there*/

GO         /*Implement the trigger*/

ALTER TRIGGER tr ON T
INSTEAD OF DELETE 
AS
BEGIN 
DELETE T
FROM T 
JOIN DELETED D 
ON T.C = D.C
END

GO

DELETE 
FROM T

SELECT * 
FROM T      /*The 2 rows are now gone*/

DROP TABLE T

Upvotes: 2

Related Questions