Implementing Triggers in SQL Server 2000

Output

Server: Msg 547, Level 16, State 1, Line 1

INSERT statement conflicted with COLUMN FOREIGN KEY constraint ‘FK_User_Details_User_Master’. The conflict occurred in database ‘KDMNN’, table ‘User_Master’, column ‘UserID’.

The statement has been terminated.

UserID 100 does not exist in the User_Master table, so the Foreign Key constraint has been checked and an error message is displayed. What we can conclude is: AFTER triggers gets executed automatically after the PK and FK constraints.

Create Trigger trgInsteadInsert

On User_Details

INSTEAD OF INSERT

AS

BEGIN

Print (‘INSTEAD OF Trigger [trgInsteadInsert] – Trigger executed !!’)

END

INSERT INTO USER_DETAILS(USERID, FNAME,LNAME, MNAME, EMAIL)

VALUES(100, ‘FName’,’LName’,’MName’,’test@test.com’)

Output

INSTEAD OF Trigger [trgInsteadInsert] – Trigger executed !!

(1 row(s) affected)

Even if the UserID 100 does not exists in the User_Master table, the trigger gets gets executed automatically.

DROP Trigger trgInsteadInsert

Unlike AFTER triggers, INSTEAD OF triggers can be created on views.

Create trigger trgOnView

on vwUserMaster

INSTEAD OF INSERT

AS

begin

             Print (‘INSTEAD OF Trigger [trgOnView]  – vwUserMaster !!!’)

End

INSERT INTO VWUSERMASTER(USERNAME, PASSWORD)

VALUES(‘Damerla’,’Venkat’)

Output

INSTEAD OF Trigger [trgOnView]  – vwUserMaster !!

(1 row(s) affected)

So whenever a user tries to insert data into the view vwUserMaster, the INSTEAD OF trigger trgOnView will automatically be executed.

In SQL SERVER 2000, views can be used to INSERT/DELETE and UPDATE the data in the multiple tables, and this can be achieved using INSTEAD OF triggers.

CREATE VIEW vwUser

AS

SELECT

             [User_Master].[Username],

             [User_Master].[Password],

             [User_Details].[FName],

             [User_Details].[MName],

             [User_Details].[LName],

             [User_Details].[Email]

FROM

             [User_Master], [User_Details]

WHERE

             [User_Master].[UserID]=[User_Details].[UserID]

CREATE TRIGGER tgrInsertData

ON vwUser

INSTEAD OF INSERT

AS

BEGIN

             Declare @UserName varchar(50)

             Declare @Password varchar(50)

             Declare @FName varchar(50)

             Declare @MName varchar(50)

             Declare @LName varchar(50)

             Declare @Email varchar(50)

             SELECT

                           @UserName = UserName,

                           @Password = Password,

                           @FName = FName,

                           @MName = MName,

                           @LName = LName,

                           @Email = Email

             FROM INSERTED

            

             INSERT INTO User_Master(UserName, Password)

             VALUES(@UserName, @Password)

            

             INSERT INTO User_Details(UserID,FName,LName,MName,Email) VALUES(@@Identity, @FName, @LName, @MName, @Email)

END

INSERT INTO vwUser(UserName,Password,FName,LName,MName,Email)

VALUES (‘Dhananjay’,’Dhananjay’,’Dhananjay’,’Nagesh’,NULL,

‘Dhananjay@kdmnn.com

Output

AFTER Trigger [trgInsert]  – Trigger executed !!

AFTER Trigger [trgInsert2]  – Trigger executed !!

AFTER Trigger [trgInsert3]  – Trigger executed !!

AFTER Trigger [trgEncrypted] Encrypted  – Trigger executed !!

(1 row(s) affected)

AFTER Trigger [trgAfterInsert] – Trigger executed !!

(1 row(s) affected)

Then check the data in the following tables User_Master and User_Details. The new row gets inserted in both the tables.

A view or table can have only one INSTEAD OF trigger for each INSERT, UPDATE and DELETE events.

Continues…

Leave a comment

Your email address will not be published.