Triggers Concepts

select * from customer_test

--------------------

create table customer_test (id int identity primary key,

name varchar(100),dept varchar(100),email varchar(100))

-------------------------

create table customer_arch (pkid int identity primary key,

id int references customer_test (id),

name varchar(100),dept varchar(100),email varchar(100))

------------------

alter table customer_arch add mode varchar(10)

alter table customer_arch add description varchar(1000)

alter table customer_arch

DROP CONSTRAINT FK__customer_arc__id__53CDB2C9

-----------------------------------------

INSERT INTO customer_test

SELECT 'GAN','SALES','GAN@GMAIL.COM'



DELETE FROM CUSTOMER_TEST

UPDATE CUSTOMER_TEST SET name = name+'P'

TRUNCATE TABLE customer_arch

SELECT * FROM customer_arch

SELECT * FROM customer_test



UPDATE CUSTOMER_TEST

SET NAME = CASE WHEN NAME = 'RAMKI' THEN 'RAM' ELSE NAME END



SELECT CASE WHEN NAME = 'RAM' THEN 'RAMKI' ELSE NAME END

FROM CUSTOMER_TEST

------------------------------------



ALTER TRIGGER TRG_CUSTOMERLOG

ON customer_test

FOR UPDATE, DELETE, INSERT

AS

BEGIN

SELECT * FROM INSERTED

SELECT * FROM DELETED

IF EXISTS (SELECT 1 FROM INSERTED) AND NOT EXISTS (SELECT 1 FROM DELETED)

BEGIN

PRINT 'RECORD INSERTED'

END



ELSE IF EXISTS (SELECT 1 FROM INSERTED I JOIN DELETED D ON D.ID = I.ID

WHERE I.NAME <> D.NAME OR I.DEPT <> D.DEPT OR I.EMAIL <> D.EMAIL)

BEGIN

PRINT 'RECORD UPDATED'

INSERT INTO customer_arch SELECT D.ID,D.NAME,D.DEPT,D.EMAIL, 'UPDATED', NULL

FROM INSERTED I JOIN DELETED D ON D.ID = I.ID

WHERE I.NAME <> D.NAME OR I.DEPT <> D.DEPT OR I.EMAIL <> D.EMAIL



END



ELSE IF NOT EXISTS (SELECT 1 FROM INSERTED) AND EXISTS (SELECT 1 FROM DELETED)

BEGIN

PRINT 'RECORD DELETED'

INSERT INTO customer_arch SELECT D.ID,D.NAME,D.DEPT,D.EMAIL,'DELETED', NULL

FROM DELETED D

END

END