SQL SERVER TRIGGER EXAMPLE

AS SALAMO ALAIKUM WA RAHMATULLAH


CREATE TABLE MSTH_COUNTRIES_XX (
COUNTRY_SYS_ID BIGINT,
COUNTRY_CODE NVARCHAR(24),
COUNTRY_NAME NVARCHAR(240),
COUNTRY_NAME_BL NVARCHAR(240),
COUNTRY_CR_UID NVARCHAR(24),
COUNTRY_CR_DT DATETIME,
COUNTRY_UPD_UID NVARCHAR(24),
COUNTRY_UPD_DT DATETIME,
COUNTRY_FLEX_01 NVARCHAR(240),
COUNTRY_FLEX_02 NVARCHAR(240),
COUNTRY_FLEX_03 NVARCHAR(240),
COUNTRY_FLEX_04 NVARCHAR(240),
COUNTRY_FLEX_05 NVARCHAR(240),
COUNTRY_DEL_UID NVARCHAR(24),
COUNTRY_DEL_DT DATETIME)

alter table MST_COUNTRIES_XX add COUNTRY_DEL_DT DATETIME;

CREATE TABLE MST_COUNTRIES_XX (
COUNTRY_SYS_ID BIGINT IDENTITY(1,1) NOT NULL,
COUNTRY_CODE NVARCHAR(24) NOT NULL,
COUNTRY_NAME NVARCHAR(240) NOT NULL,
COUNTRY_NAME_BL NVARCHAR(240),
COUNTRY_CR_UID NVARCHAR(24) NOT NULL,
COUNTRY_CR_DT DATETIME NOT NULL,
COUNTRY_UPD_UID NVARCHAR(24),
COUNTRY_UPD_DT DATETIME,
COUNTRY_FLEX_01 NVARCHAR(240),
COUNTRY_FLEX_02 NVARCHAR(240),
COUNTRY_FLEX_03 NVARCHAR(240),
COUNTRY_FLEX_04 NVARCHAR(240),
COUNTRY_FLEX_05 NVARCHAR(240),
CONSTRAINT uQ_01_COUNTRY_XX UNIQUE (COUNTRY_CODE),
CONSTRAINT PK_COUNTRY_XX PRIMARY KEY( COUNTRY_SYS_ID ));



Create trigger TRGUPDATE ON MST_COUNTRIES_XX
FOR UPDATE
AS
BEGIN
Declare @COUNTRY_SYS_ID bigint;
Declare @COUNTRY_CODE NVARCHAR(24);
Declare @COUNTRY_NAME NVARCHAR(240);
Declare @COUNTRY_NAME_BL NVARCHAR(240);
Declare @COUNTRY_CR_UID NVARCHAR(24);
Declare @COUNTRY_CR_DT DATETIME;
Declare @COUNTRY_UPD_UID NVARCHAR(24);
Declare @COUNTRY_UPD_DT DATETIME;
Declare @COUNTRY_FLEX_01 NVARCHAR(240);
Declare @COUNTRY_FLEX_02 NVARCHAR(240);
Declare @COUNTRY_FLEX_03 NVARCHAR(240);
Declare @COUNTRY_FLEX_04 NVARCHAR(240);
Declare @COUNTRY_FLEX_05 NVARCHAR(240);
Declare @COUNTRY_DEL_UID VARCHAR(24);
Declare @COUNTRY_DEL_DT DATETIME;

Select @COUNTRY_SYS_ID=xx.COUNTRY_SYS_ID  from inserted xx;
Select @COUNTRY_CODE=xx.COUNTRY_CODE from inserted xx;
Select @COUNTRY_NAME=xx.COUNTRY_NAME from inserted xx;
Select @COUNTRY_NAME_BL=xx.COUNTRY_NAME_BL from inserted xx;
Select @COUNTRY_CR_UID=xx.COUNTRY_CR_UID from inserted xx;
Select @COUNTRY_CR_DT=xx.COUNTRY_CR_DT from inserted xx;
Select @COUNTRY_UPD_UID=xx.COUNTRY_UPD_UID from inserted xx;
Select @COUNTRY_UPD_DT=xx.COUNTRY_UPD_DT from inserted xx;
Select @COUNTRY_FLEX_01=xx.COUNTRY_FLEX_01 from inserted xx;
Select @COUNTRY_FLEX_02=xx.COUNTRY_FLEX_02 from inserted xx;
Select @COUNTRY_FLEX_03=xx.COUNTRY_FLEX_03 from inserted xx;
Select @COUNTRY_FLEX_04=xx.COUNTRY_FLEX_04 from inserted xx;
Select @COUNTRY_FLEX_05=xx.COUNTRY_FLEX_05 from inserted xx;
Select @COUNTRY_DEL_UID=xx.COUNTRY_DEL_UID from inserted xx;
Select @COUNTRY_DEL_DT=xx.COUNTRY_DEL_DT from inserted xx;

Insert into MSTH_COUNTRIES_XX (COUNTRY_SYS_ID,COUNTRY_CODE,COUNTRY_NAME,COUNTRY_NAME_BL,COUNTRY_CR_UID,COUNTRY_CR_DT,COUNTRY_UPD_UID,COUNTRY_UPD_DT,
COUNTRY_FLEX_01,COUNTRY_FLEX_02,
COUNTRY_FLEX_03,
COUNTRY_FLEX_04,
COUNTRY_FLEX_05,
COUNTRY_DEL_UID,
COUNTRY_DEL_DT)

values (@COUNTRY_SYS_ID,@COUNTRY_CODE,@COUNTRY_NAME,@COUNTRY_NAME_BL,@COUNTRY_CR_UID,@COUNTRY_CR_DT,@COUNTRY_UPD_UID,@COUNTRY_UPD_DT,@COUNTRY_FLEX_01
,@COUNTRY_FLEX_02,@COUNTRY_FLEX_03,@COUNTRY_FLEX_04,@COUNTRY_FLEX_05,@COUNTRY_DEL_UID,@COUNTRY_DEL_DT);

PRINT 'AFTER UPDATE TRIGGER FIRED';
END;  


--======================== TRIGGER FOR DELETION
Create trigger TRGDELETE ON MST_COUNTRIES_XX
AFTER DELETE
AS
BEGIN
Declare @COUNTRY_SYS_ID bigint;
Declare @COUNTRY_CODE NVARCHAR(24);
Declare @COUNTRY_NAME NVARCHAR(240);
Declare @COUNTRY_NAME_BL NVARCHAR(240);
Declare @COUNTRY_CR_UID NVARCHAR(24);
Declare @COUNTRY_CR_DT DATETIME;
Declare @COUNTRY_UPD_UID NVARCHAR(24);
Declare @COUNTRY_UPD_DT DATETIME;
Declare @COUNTRY_FLEX_01 NVARCHAR(240);
Declare @COUNTRY_FLEX_02 NVARCHAR(240);
Declare @COUNTRY_FLEX_03 NVARCHAR(240);
Declare @COUNTRY_FLEX_04 NVARCHAR(240);
Declare @COUNTRY_FLEX_05 NVARCHAR(240);
Declare @COUNTRY_DEL_UID VARCHAR(24);
Declare @COUNTRY_DEL_DT DATETIME;

Select @COUNTRY_SYS_ID=xx.COUNTRY_SYS_ID  from deleted xx;
Select @COUNTRY_CODE=xx.COUNTRY_CODE from deleted xx;
Select @COUNTRY_NAME=xx.COUNTRY_NAME from deleted xx;
Select @COUNTRY_NAME_BL=xx.COUNTRY_NAME_BL from deleted xx;
Select @COUNTRY_CR_UID=xx.COUNTRY_CR_UID from deleted xx;
Select @COUNTRY_CR_DT=xx.COUNTRY_CR_DT from deleted xx;
Select @COUNTRY_UPD_UID=xx.COUNTRY_UPD_UID from deleted xx;
Select @COUNTRY_UPD_DT=xx.COUNTRY_UPD_DT from deleted xx;
Select @COUNTRY_FLEX_01=xx.COUNTRY_FLEX_01 from deleted xx;
Select @COUNTRY_FLEX_02=xx.COUNTRY_FLEX_02 from deleted xx;
Select @COUNTRY_FLEX_03=xx.COUNTRY_FLEX_03 from deleted xx;
Select @COUNTRY_FLEX_04=xx.COUNTRY_FLEX_04 from deleted xx;
Select @COUNTRY_FLEX_05=xx.COUNTRY_FLEX_05 from deleted xx;
Select @COUNTRY_DEL_UID=xx.COUNTRY_DEL_UID from deleted xx;
Select @COUNTRY_DEL_DT=xx.COUNTRY_DEL_DT from deleted xx;

Insert into MSTH_COUNTRIES_XX (COUNTRY_SYS_ID,COUNTRY_CODE,COUNTRY_NAME,COUNTRY_NAME_BL,COUNTRY_CR_UID,COUNTRY_CR_DT,COUNTRY_UPD_UID,COUNTRY_UPD_DT,
COUNTRY_FLEX_01,COUNTRY_FLEX_02,
COUNTRY_FLEX_03,
COUNTRY_FLEX_04,
COUNTRY_FLEX_05,
COUNTRY_DEL_UID,
COUNTRY_DEL_DT)

values (@COUNTRY_SYS_ID,@COUNTRY_CODE,@COUNTRY_NAME,@COUNTRY_NAME_BL,@COUNTRY_CR_UID,@COUNTRY_CR_DT,@COUNTRY_UPD_UID,@COUNTRY_UPD_DT,@COUNTRY_FLEX_01
,@COUNTRY_FLEX_02,@COUNTRY_FLEX_03,@COUNTRY_FLEX_04,@COUNTRY_FLEX_05,@COUNTRY_DEL_UID,@COUNTRY_DEL_DT);

PRINT 'AFTER DELETE TRIGGER FIRED';
END;

DELETE FROM MST_COUNTRIES_XX
WHERE COUNTRY_SYS_ID=2




--
MA ASALAAM
PASSION FOR ORACLE