How to write a update,delete trigger in sqlserver2008

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

, +

hii to all,
I have a requirement that,i need to migrate or copy the data between two tables.Actually i have two tables Mainlog(in user db) & Sparelog(in users2 db) with same structure present in different databases,now if any data is inserted into mainlog table it should automatically copy to sparelog table, i have done this by writing some insert trigger like below:

SQL

CREATE TRIGGER InsertLogRecords
ON mainlog FOR INSERT
AS
BEGIN
 SET NOCOUNT ON
 IF EXISTS(SELECT * FROM mainlog)
  INSERT INTO users2.dbo.sparelog
  SELECT * FROM inserted
 ELSE
  INSERT INTO users2.dbo.sparelog
  SELECT * FROM inserted
END

The above trigger is working fine and copying the data to sparelog table automatically when ever any data is inserted.Now even i want the same action when update and delete operation is performed to mainlog table the same update,delete action should automatically reflect to sparelog table.Please guide how can i do this with single trigger or with two triggers like update,delete triggers,please try to help me with code.thanks in advance

Posted 27-Feb-13 21:04pm

Ram7 from Hyderabad

Updated 27-Feb-13 21:40pm

v3

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Babu Kumar · Accepted Answer · 2013-02-27T22:00:00

Solution 1

Hi Ram,

SQL

CREATE TRIGGER DeleteLogRecords
ON mainlog FOR DELETE
AS
BEGIN
 SET NOCOUNT ON
 IF EXISTS(SELECT * FROM mainlog)
  INSERT INTO users2.dbo.sparelog
  SELECT * FROM deleted
 ELSE
  INSERT INTO users2.dbo.sparelog
  SELECT * FROM deleted
END

XML

<pre lang="sql">CREATE TRIGGER UpdateLogRecords
ON mainlog FOR UPDATE
AS
BEGIN
 SET NOCOUNT ON
 IF EXISTS(SELECT * FROM mainlog)
  INSERT INTO users2.dbo.sparelog
  SELECT * FROM Inserted
 ELSE
  INSERT INTO users2.dbo.sparelog
  SELECT * FROM Inserted
END</pre>

Check the above query.

Hope its useful

Regards,
Babu.K

Posted 27-Feb-13 22:00pm

Babu Kumar

Comments

Ram7 from Hyderabad 28-Feb-13 4:16am

Dear Babu,thanks for ur quick responce, i tried with ur above update,delete triggers,they were executed successfully, but here when delete trigger is executed the selected record is being deleted from mainlog table(source table) and in sparelog table it is inserting as a new record(duplicate record)Here my requirement is, the selected record from mainlog for delete it also should delete from sparelog table,rather inserting as a new duplicate record,the same thing is happing with update trigger also,the source table should be an exact replica of target table,please tell me how to rectify the above triggers to meet my exact requirement

Babu Kumar 28-Feb-13 4:26am

CREATE TRIGGER DeleteLogRecords
ON mainlog FOR DELETE
AS
BEGIN
DELETE FROM users2.dbo.sparelog WHERE users2.dbo.sparelog.ID=deleted.ID
END

Here "users2.dbo.sparelog.ID=deleted.ID" ID is some common column value to be mapped for delelet.

CREATE TRIGGER UpdateLogRecords
ON mainlog FOR UPDATE
AS
BEGIN
UPDATE users2.dbo.sparelog SET users2.dbo.sparelog.Column1=Inserted.Column1 WHERE users2.dbo.sparelog.ID=Inserted.ID
END

Check the above Query.

Regards,
Babu.K

Ram7 from Hyderabad 28-Feb-13 4:44am

I have tried with ur above trigger,but iam finding errors like
Error:-"The multi-part identifier "deleted.Age" could not be bound"
I wrote the trigger like this....
"DELETE FROM users2.dbo.sparelog WHERE users2.dbo.sparelog.empid=deleted.empid"
I think iam wrong with where condition itself,i have the table structure with 5 columns like this:empid,Firstname,Lastname,Location,Age
can u please tell me where iam doing the mistake

Babu Kumar 28-Feb-13 4:54am

Can you post the trigger and your table structure of both the table

Ram7 from Hyderabad 28-Feb-13 5:05am

Both tables mainlog&sparelog have similar structure with same datatypes,like this with 5columns
empid Firstname Lastname Location Age
and update trigger like what u have given:-
CREATE TRIGGER UpdateLogRecords
ON mainlog FOR UPDATE
AS
BEGIN
UPDATE users2.dbo.sparelog SET users2.dbo.sparelog.Age=Inserted.Age WHERE users2.dbo.sparelog.empid=Inserted.empid
END
Delete trigger like this:-
CREATE TRIGGER DeleteLogRecords
ON mainlog FOR DELETE
AS
BEGIN
DELETE FROM users2.dbo.sparelog WHERE users2.dbo.sparelog.empid=deleted.empid
END
Iam new to this DB coding,i think iam wrong with giving correct values with where condition..
thanks
Ramu

Babu Kumar 28-Feb-13 5:10am

Use IN Opereator instead of EQUAL Operator.
i've mentioned sample query in the below post. Check that.

Babu Kumar 28-Feb-13 5:09am

Hi Use like this,

ALTER TRIGGER DeleteLogRecords
ON employee FOR DELETE
AS
BEGIN
DELETE FROM employeedummy WHERE employeedummy.id in (SELECT deleted.ID from deleted)
END

like that for update.

Regards,
Babu.K

Ram7 from Hyderabad 28-Feb-13 5:16am

Ya kumar,its working thanks for all,

Babu Kumar 28-Feb-13 5:19am

Welcome Ram, Rate my answer if you like :)

Ram7 from Hyderabad 28-Feb-13 5:43am

ya sure definitely

Ram7 from Hyderabad 28-Feb-13 5:55am

Dear kumar,sorry for disturbing u again, iam finding again errors with update please help me,this is the trigger iam writing for update
create TRIGGER updateLogRecords
ON mainlog FOR update
AS
BEGIN
update users2.dbo.sparelog set users2.dbo.sparelog.empid in(inserted.empid WHERE users2.dbo.sparelog.empid =Inserted.empid)
END
thanks
Ramu

Babu Kumar 28-Feb-13 6:06am

Hi,
User the Update Like below sample query

CREATE TRIGGER [dbo].[UpdateLogRecords]
ON [dbo].[employee] FOR UPDATE
AS
BEGIN
UPDATE employeedummy SET first_name=(SELECT inserted.first_name FROM inserted)
WHERE employeedummy.id in (SELECT inserted.ID from inserted)
END

Regards,
Babu.K

How to write a update,delete trigger in sqlserver2008

1 solution

Solution 1

Add your solution here

Preview 0

How to write a update,delete trigger in sqlserver2008

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us