Posted By

kashif21 on 04/22/11

Tagged

Versions (?)

Last Edited at 04/22/11 11:38pm

Statistics

Viewed 908 times

Favorited by 0 user(s)

Related snippets

Complete example of Triggers in insert,Update and delete scenario

/ Published in: SQL

Highlighted TextExpand

2> select * from employee
3> GO
ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          1 Jason            40420 1994-02-01 00:00:00.000 New York   W
          2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
          3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W
          4 Linda            40620 1997-11-04 00:00:00.000 New York   N
          5 David            80026 1998-10-05 00:00:00.000 Vancouver  W
          6 James            70060 1999-09-06 00:00:00.000 Toronto    N
          7 Alison           90620 2000-08-07 00:00:00.000 New York   W
          8 Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
          9 Mary             60020 2002-06-09 00:00:00.000 Toronto    W

(9 rows affected)
1>
2> CREATE TABLE myArchive (
3>    AID         int           IDENTITY(1,1) PRIMARY KEY CLUSTERED,
4>    type        nvarchar(6)   NOT NULL,
5>    whenchanged smalldatetime NOT NULL DEFAULT Getdate(),
6>    ID int,
7>    newName nvarchar(30),
8>    oldName nvarchar(50)
9> )
10> GO
1>
2>
3> CREATE TRIGGER myTriggerINSERT
4> ON Employee
5> FOR INSERT
6> AS
7> DECLARE @ID int, @Name nvarchar(30)
8>
9> SET @ID = (SELECT ID FROM inserted)
10> SET @Name = (SELECT Name FROM inserted)
11>
12> INSERT myArchive (type, ID, newName) VALUES('INSERT', @ID, @Name)
13> GO
1>
2>
3> CREATE TRIGGER myTriggerDELETE
4> ON Employee
5> FOR DELETE
6> AS
7> DECLARE @ID int, @Name nvarchar(30)
8>
9> SET @ID = (SELECT ID FROM deleted)
10> SET @Name = (SELECT Name FROM deleted)
11>
12> INSERT myArchive (type, ID, oldName ) VALUES('DELETE', @ID, @Name)
13> GO
1>
2> CREATE TRIGGER myTriggerUPDATE
3> ON Employee
4> INSTEAD OF UPDATE
5> AS
6>
7> DECLARE @ID int, @newName nvarchar(30), @oldName nvarchar(30)
8>
9> IF (SELECT ID FROM inserted) <> (SELECT ID FROM deleted)
10>     RAISERROR ('You are not allowed to change ID.', 10,1)
11> ELSE
12> BEGIN
13>
14> --set local variables
15> SET @ID = (SELECT ID FROM inserted)
16> SET @newName = (SELECT Name FROM inserted)
17> SET @oldName = (SELECT Name FROM deleted)
18>
19> --write to table
20> UPDATE Employee SET Name = @newName WHERE ID = @ID
21> -- write to archive
22> INSERT myArchive (type, ID, newName, oldName) VALUES('UPDATE', @ID, @newName, @oldName)
23> END
24> GO
1>
2> INSERT Employee (id, name) VALUES (13, 'Rickie')
3> GO

(1 rows affected)
1>
2> SELECT * FROM myArchive
3> GO
AID         type   whenchanged          ID          newName                        oldName
----------- ------ -------------------- ----------- ------------------------------ ------------------
          1 INSERT  2006-10-10 20:21:00          13 Rickie                         NULL

(1 rows affected)
1>
2> UPDATE Employee
3> SET Name = 'Rick'
4> WHERE ID = 3
5> GO

(1 rows affected)

Report this snippet Tweet