Trigger - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Trigger

Author

Topic

Jonny1409
Posting Yak Master

133 Posts

Posted - 2006-05-30 : 08:44:24

Hello,

I want to create a trigger on tblA which when a record is deleted from it, it will automatically be removed and added into tblB.

Firstly, is this possible ?
And If so, how can I go about it ?

Thanks in advance,
J.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-30 : 08:50:07

[code]create trigger td_table1 on tbla for delete
begin
insert into tblb(col1, col2, col3 . . .)
select cola, colb, colc, . . .
from deleted
end[/code]

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-30 : 08:51:23

yeah its possible

you have to write a delete trigger on the tbla

somthing like this
Create Trigger Trg_del_tbla
on TblA
for Delete
AS
Delete From TblB where FK in (Select PK From Deleted)

As
Delete From Tblb

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-30 : 09:09:57

quote:
Originally posted by chiragkhabaria

yeah its possible

you have to write a delete trigger on the tbla

somthing like this
Create Trigger Trg_del_tbla
on TblA
for Delete
AS
Delete From TblB where FK in (Select PK From Deleted)

As
Delete From Tblb

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Tan's code is the correct one

Madhivanan

Failing to plan is Planning to fail

Jonny1409
Posting Yak Master

133 Posts

Posted - 2006-05-30 : 10:36:11

Thanks for your replies.

I've tried that, and it doesn't work due to (i assume) one of the fields in the tables being of type 'ntext' as I get the following error :

Error 311: cannot use text, ntext, or image columns.......

Is there a way around this ?

Thanks,
J

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-30 : 11:15:33

I think you can get around that using an INSTEAD OF trigger. That way you can use a SELECT from the actual table instead of the DELELTED table. You just have to remember to do the user delete yourself in the trigger:


create trigger td_table1 on tbla instead of delete
as
begin
    --insert what is about to be deleted
    insert into tblb(col1, col2, col3)
    select t.cola, t.colb, t.colc
    from deleted d
    inner join td_table1 t
		on t.<pk> = d.<pk>

    --now perform the user delete 
    --(this is not done automatcially because of the instead of trigger)
    delete t
    from deleted d
    inner join td_table1 t
		on t.<pk> = d.<pk>
end

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources