Monday, March 19, 2012

Need an example of a trigger.

Hello,

I'm a newbie to the MS SQL db engine, and I'd like to have some help with Transact-SQL. I'd like to know the code to create a trigger to do the following. I have two tables, one references the other. I'd like the database to automatically delete the records from table2 when the record that they reference in table1 is deleted. Much thanks.Try this

create trigger dbo.trigger_deleterecs on table1
for delete
as
begin

declare @.refcolumn datatypehere
select @.refcolumn = deleted.refcolumn from deleted
delete from table2 where table2refcolumn = @.refcolumn

end|||how about:

create trigger dbo.trigger_deleterecs on table1
for delete
as
begin
delete from table2 where refcolumn in (select refcolumn from deleted)
end

or

create trigger dbo.trigger_deleterecs on table1
for delete
as
begin
delete t2
from table2 t2
join deleted d on t2.refcolumn = d.refcolumn
end

by using either of these you will delete all records when multipule records are deleted.

No comments:

Post a Comment