Updating another table via Update trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Updating another table via Update trigger

Author

Topic

BEAR_Ilko
Starting Member

3 Posts

Posted - 2009-11-12 : 05:13:03

**Sorry for bumping, but I need this issue resolved to the end of this week.**
Hello to all! I'm still new with triggers but I get the basic idea.

Here is the whole situation:
I'm trying to make two tables exactly the same PLUTable and PLUTable2 - they have around 69 columns. They both are in separated databases - tester and tester2 on the local computer.
The main one is PLUTable.
The software that we are given only works with PLUTable.
So we are trying to send the info to PLUTable2 (we want it to be on another server but for now just locally).

Overall the idea is when someone makes changes to the 1st table - it affects the 2nd.
I can just write the whole 69 column names, but there must be a better way.

I already have this as an Insert trigger:
CREATE TRIGGER dbo.Insert_Trig ON dbo.PLUTable
after Insert
AS
INSERT INTO tester2.dbo.PLUTable2 SELECT * FROM Inserted

It works when we insert new record.

Currently the update trigger is...

CREATE trigger dbo.Update_Trig
on dbo.PLUTable
for update as
begin
update tester2.dbo.PLUTable2
set PLUNUM = inserted.PLUNUM
from PLUTable, deleted, inserted
where deleted.PLUNUM = tester2.dbo.PLUTable2.PLUNUM
end

...and updates just column PLUNUM

Any ideas how can I update the PLUTable2 when the PLUTable is getting updated?

Thanks in advance!

BEAR_Ilko
Starting Member

3 Posts

Posted - 2009-11-16 : 08:52:59

Sorry for bumping, but I need this issue resolved to the end of this week.
Any ideas anyone?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-16 : 09:14:45

Might want to use replication for this. But...


CREATE trigger dbo.Update_Trig on dbo.PLUTable
for update as
begin
update	t 
set	col1 = i.col1,
	col2 = i.col2,
	col3 = i.col3,
	--etc
from	tester2.dbo.PLUTable2 t
join	inserted i
on	i.pkField = t.pkField
End

If you don't want to write all the field names, you can drag 'em out of the object explorer by drag and dropping "columns" when you expand the table.

Or you can be real lazy and do it like this


CREATE trigger dbo.Update_Trig on dbo.PLUTable
for update as
begin

DELETE	t
from	tester2.dbo.PLUTable2 t
join	inserted i
on	i.pkField = t.pkField

INSERT	tester2.dbo.PLUTable2
SELECT * FROM inserted
END

MuadDBA

628 Posts

Posted - 2009-11-16 : 12:59:51

Is there a reason replication can't be used for this? It does everything you want, and is much easier to set up and maintain.

BEAR_Ilko
Starting Member

3 Posts

Posted - 2009-11-17 : 03:10:40

Thanks russell! I already tried one other "lazy way" but it didn't work. However your way worked :). Thanks for the help.

I am unfamiliar with replication. Can you give me some information?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-17 : 08:35:56

Glad to help.

Transactional Replication is what you'd be interested in for this...

Start here: http://msdn.microsoft.com/en-us/library/ms151198(SQL.90).aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources