delete trigger 1

[oakpark5]

I'm trying to create a trigger that when a record is deleted in one table the same record will be deleted from another table. The tables are exactly the same, just in different databases. I keep getting an error saying i'm not declaring @dcustno? Thanks in advance.

create trigger trig_deleteicitem
on pro30...testsql
for delete
as
declare @dcustno
select @dcustno = (select custno from deleted)
delete from pkpro...testsql where custno = @dcustno

Software Engineer
Team Lead
damn bill gates, he has everything

 

[RobertT687]

Couple of problems I see.
1) You aren't specifying the datatype in your Declare of @dcustno.
2) Your trigger is assuming a single row at a time deletion. A trigger should be written to handle action on multiple rows.

Assuming you have sufficient privilege between the databases, this should work:
DELETE FROM pkpro...testsql t, deleted
WHERE t.custno = deleted.custno

 

[SQLBill]

Typo check:

Isn't there one too many periods (.) in this:

pkpro...testsql

Shouldn't it be pkpro..testsql?

-SQLBill

 

[oakpark5]

yes, too many periods, and i rewrote it like this:

create trigger trig_deleteicitem
on pro30...testsql
for delete
as
delete from pkpro...testsql where custno = (select custno from deleted)

and that worked, thanks......heading to lens crafters....

Software Engineer
Team Lead
damn bill gates, he has everything

 

[oakpark5]

ok, now i'm trying to use an update...
what i'm trying to do is, if an update occurs on one table, i want the exact same thing to happen on the other table.
This is what i have thus far:
create trigger trig_updateicitem
on pro30..sqltest
for update
as
insert into pkpro..testsql(select * from deleted)
select * from inserted

Kinda stuck with this one...

Software Engineer
Team Lead
damn bill gates, he has everything

 

[ESquared]

if it's "for update" then you would do

UPDATE t
SET
   t.field1=i.field1,
   t.field2=t.field2,
   t.field3=t.field3,
   [and so on...]
FROM pkpro..testsql t
   INNER JOIN inserted i ON t.primarykey = i.primarykey

You could do this by deleting and inserting and thus not have to name columns, but I truly don't recommend it. First of all, it won't work if your tables have an identity column.

More importantly, this is exactly the sort of thing that replication is for. Instead of reinventing the wheel, why don't you use the built-in replication features of SQL server?