Info, Tutorials, Downloads, Audio Books, Videos, Ebooks and More

Wednesday, July 5, 2017

Update Trigger for Sql Server

Update Trigger is very useful if you keep your deleted record or which data is replaced with new , here i have one table called tblstudent which have a user details, user may update their information time to time and i have to keep their deleted data securely, so lets discuss how it is possible lets create table tblstudent

create table tblstudent
(
studentid int primary key,
studentname varchar(max),
studentaddress varchar(max),
grade varchar(10),
isactive bit
)

Now I have to create one more table call tblstudent_log which store the updated data from tblstudent

create table tblstudent_log
(
studentid int,
studentname varchar(max),
studentaddress varchar(max),
grade varchar(10),
isactive bit,
updatedate
)

Now i am going to create trigger 

Create  trigger [dbo].[updateforstudent]
on tblstudent
for Update 
as
begin
insert into tblstudent_log 
(
select studentid,
studentname,
studentaddress,
grade,
isactive,
getdate()
from deleted
)
end

Here updateforstudent is a trigger name to execute whenever update to tblstudent, because we are creating this trigger on tblstudentand for update this statement tell to execute whenever update command pass to tblstudent
same thing i insert to tblstudent_log by selecting data from theselect studentid,
studentname,
studentaddress,
grade,
isactive,
getdate()
from deleted,
 deleted is a magic table which store the deleted data when we update the tblstudent

Recommended Articles :


No comments:

Post a Comment

Dear Readers,

Great !! You've decided to leave comment for us. Let's make meaningful conversation by adding your name.

Please note that,

Comments deemed to be spam or questionable spam, including profanity and containing language or concepts that could be deemed offensive will be deleted.
Including a link to relevant content is permitted, but comments should be relevant to the post topic.