Saturday, 14 September 2013

trigger for INSERT UPDATE DELETE in single statement


trigger for INSERT UPDATE DELETE in single statement


CREATE TRIGGER TRG ON V
FOR INSERT,UPDATE,DELETE
AS
BEGIN
IF EXISTS(SELECT * FROM inserted)
BEGIN
INSERT INTO V_log(ID,NAME,flag) SELECT *,'INSERTED' FROM inserted
END
IF EXISTS(SELECT * FROM deleted)
BEGIN
INSERT INTO V_log(ID,NAME,flag) SELECT *,'DELETED' FROM deleted
END
IF EXISTS(SELECT * FROM deleted)
IF EXISTS(SELECT * FROM inserted)
BEGIN
INSERT INTO V_log(ID,NAME,flag) SELECT *,'UPDATED' FROM inserted
END
END

EMP, DEPT Sample script

/****** Object:  Table [dbo].[DEPT]    Script Date: 19-05-2016 06:58:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET A...