Saturday, June 26, 2010

Trigger in Sql Server

Here is the sample of how to write trigger in sql server . The first one(InsertIntoPlayerProfile) will fire when record inserted into the  tbl_Player it will automatically insert the PlayerId into the tbl_PlayerProfile where table inserted is the temp table created when any table of the database is modified due to insert or update query.deleted is also the temp table created when any record deleted from the table.


CREATE  TRIGGER [dbo].[InsertIntoPlayerProfile]
ON [dbo].[tbl_Player]
AFTER INSERT
AS
Insert Into tbl_PlayerProfile(PlayerId)
SELECT i.PlayerId
FROM inserted AS i

Now for the Update 

CREATE TRIGGER [dbo].[UpdateIntoLogin]
ON [dbo].[tbl_Player]
AFTER Update
AS
   Update tbl_Login
Set Email=(SELECT i.Email
    FROM inserted AS i)
Where
UserName=(SELECT i.UserName
    FROM inserted AS i)


Now for Delete

CREATE TRIGGER [dbo].[DeleteFromPlayerProfile]
ON [dbo].[tbl_Player]
AFTER Delete
AS
--Delete Record from the tbl_PlayerProfile table for the related PlayerId
Delete from tbl_PlayerProfile
WHERE EXISTS
(
    SELECT d.PlayerId
    FROM deleted AS d
    WHERE tbl_PlayerProfile.PlayerId=d.PlayerId
)
 

1 comment:

  1. very nice article.Good programing and technical stuffs in .net development.Keep it up!!.
    People who are looking for jobs in software just take look on sliceindia.com and grab your good opportunity.

    ReplyDelete