Multi column delete 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

SQL Server 2000 Forums

SQL Server Development (2000)

Multi column delete trigger??

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-11-19 : 17:38:43

Hi all,
I have a table, PS_TKT_HDR, with a column TKT_NO. I also have a table TIMETABLE, with columns TIME01, TIME02, TIME03.
TIMETABLE also has columns City01, City02, City03.
we populate TIMETABLE.TIME0X columns using values from TKT_NO, and then a trigger on TIMETABLE fills in the corresponding City0x column from PS_TKT_HDR.
Here is the problem:
I would like a trigger on PS_TKT_HDR so that when an operator DELETES a ticket, it will also delete all info from TIMETABLE for that ticket number, resetting the TIMETABLE values to <null>.
Any ideas?

Thanks in advance,
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-19 : 17:50:43

something like


create trigger trg_PS_TKT_HDR on PS_TKT_HDR 
for delete
as
update t1
set TIME01 = null, 
    TIME02 = null, 
    TIME03 = null,
    City01 = null, 
    City02 = null, 
    City03 = null
from timetable t1 
      inner join deleted d on d.tkt_no = t1.time01

Go with the flow & have fun! Else fight the flow

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-11-19 : 18:01:58

thats almost it!
Trouble is,
the ticket number may be in any one of the TIME0x columns, not just in TIME01. if the ticket is scheduled for TIME02, then TIME02 will have the TKT_NO value in it, City02 will have the city value, and all other columns will be null...

There's never enough time to type code right,
but always enough time for a hotfix...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-19 : 18:08:29

so if time01 is the correct ticket number what values do other time0x have?

Go with the flow & have fun! Else fight the flow

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2004-11-19 : 18:18:59

I think I answered my own question!
Basically it works like this:
each TIME0x column has matching CITY0x columns. when we insert a value into a TIME column (say time01, then the corresponding CITY column is updated as well. All other columns stay null. So what I really needed to do was make an idividual trigger for each TIME0x column!
Something like:
create trigger trg_PS_TKT_HDR on PS_TKT_HDR
for delete
as
update t1
set TIME01 = null,
City01 = null,
from timetable t1
inner join deleted d on d.tkt_no = t1.time01

update t1
set TIME02 = null,
City02 = null

from timetable t1
inner join deleted d on d.tkt_no = t1.time02

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources