Feeds:
Posts
Comments

Posts Tagged ‘trigger example’

In order to show step by step how a trigger works i have created
two tables t_sample and t_sample_log.

I will create trigger on table t_sample and whenever a Trigger
is fired on this table for each INSERT,DELETE and UPDATE, i will capture and
push these changes from Inserted and Deleted tables to t_sample_log

 

 

Step 1:Create two tables  t_sample and t_sample_log.

 

CREATE
TABLE [dbo].[t_sample](

 

[col1]
[int] NULL,

 

[col2]
[decimal](20, 5) NULL

 

) ON [PRIMARY]

 

 

CREATE
TABLE [dbo].[t_sample_log](

 

[col1]
[int] NULL,

 

[col2]
[decimal](20, 5) NULL,

 

[tmpstamp]
[datetime] NULL,

 

[DML]
[varchar](20) NULL

 

) ON [PRIMARY]

 

GO

 

Deleted and inserted are logical tables. They are
having simialar schema to table on which triggers are created.

 

The deleted table will contain all rows that are removed  from
table during DELETE and UPDATE operation where as Inserted table will contain
all added to table during INSERT and UPDATE opearrtion

 

For example see below

 

Step 2: Now create Triggers on t_sample for all three
cases(INSERT,UPDATE,DELETE)

 

CREATE
TRIGGER [dbo].[TriggerTest]

 

ON 
[t_sample]

 

AFTER
INSERT,DELETE,UPDATE

 

AS

 

BEGIN

 

INSERT
INTO t_sample_log

 

SELECT
col1,col2,GETDATE(),’I’

 

FROM
Inserted

 

INSERT
INTO t_sample_log

 

SELECT
col1,col2,GETDATE(),’D’

 

FROM
Deleted

 

END

 

 

 

Now , run insert and update command one by one and check corresponding
values in  table t_sample_log.

 

Step 3:Run insert command to fire Insert Trigger

 

INSERT
INTO  t_sample

SELECT
100,100

 

Check value in Log table now

SELECT
*  from
t_sample_log

Col1     Col2                tmpstamp                               DML   

100     100.00000     2011-03-21 22:41:38.527    I

 

 

Step 4:Now, Run update command to fire update
Trigger

 

UPDATE  
t_sample

SET
col1=-100

WHERE
col1=100

 

Check again

 

SELECT
*  from
t_sample_log

Col1     Col2                tmpstamp                               DML   

100     100.00000     2011-03-21 22:41:38.527    I

-100    100.00000     2011-03-21 22:47:20.667    I

100     100.00000     2011-03-21 22:47:20.667    D

 

 

Step 5:Finally  delete trigger

 

DELETE
FROM t_sample

WHERE
col1=-100

 

Check values now

SELECT *  from t_sample_log

Col1     Col2                tmpstamp                               DML

100     100.00000     2011-03-21 22:41:38.527    I

-100    100.00000     2011-03-21 22:47:20.667    I

100     100.00000     2011-03-21 22:47:20.667    D           

-100    100.00000     2011-03-21 22:49:42.740    D

                                                                                                                          

Read Full Post »

%d bloggers like this: