Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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:
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 22:04pm
Edited 27-Feb-13 22:40pm
v3

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi Ram,
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
 
<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
  Permalink  
Comments
Ram7 from Hyderabad at 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 at 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 at 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 at 28-Feb-13 4:54am
   
Can you post the trigger and your table structure of both the table
Ram7 from Hyderabad at 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 at 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 at 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 at 28-Feb-13 5:16am
   
Ya kumar,its working thanks for all,
Babu Kumar at 28-Feb-13 5:19am
   
Welcome Ram, Rate my answer if you like :)
Ram7 from Hyderabad at 28-Feb-13 5:43am
   
ya sure definitely
Ram7 from Hyderabad at 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 at 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

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



Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 28 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100