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)

Trigger

Author

Topic

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-06-22 : 10:09:17

Going through examples of triggers in book I have here and i'm pretty lost on them so tring to pick code apart to help me understand it better.
Where does the Select 'True' line come from?
Could also use some help on how to read this trigger if anyone cares to try to explain. I having problem mainly understanding why it joins. Thanks

create trigger CustomerHasOrders
on customers
for delete
as
If exists
(
Select 'True'
from deleted d
join orders o
on d.customerid = o.customerid
)
begin raiserror ('Customer has order history. Delete failed!', 16,1)
rollback tran
end

Keithc MCSE MCSA

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-06-22 : 10:15:24

Hi Keith,

'True" is the trigger is a literal, not a column, it could have been 1, or 'a_literal' or anything else valid, for example, select *. It has no greater meaning, but is supposed to be more readable :-)

basically, the trigger is joining from the "deleted" table, which should have rows that have been deleted, to the orders table, where those customer may have children rows.

If it finds any matches, then it means that the parent row (defined on customerid which would be unqiue in this example, from the customer table) exists in the delete table, AND in the orders table - i.e. it must not be deleted. Hence the message

HTH

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-06-22 : 11:50:03

very cool thanks a ton , that helps me alot understanding where the 'True' came from. Also Great explanation of what the trigger is actually doing as well.

One more thing maybe you can help me with is:
I get confused with like an insert or delete statement used containing a join.
For ex:
Insert into orders2
select * from orders --This inserts all from orders2 into orders

With a trigger:

Insert into orders2
select i.*
from inserted i
left join orders2 o2
on i.orderid = o2.orderid
where o2.orderid is null

Does the above take all values from trigger
inserted table and actually insert
records into the orders2 table
where there was no value in orders2
table corresponding to the trigger
inserted table?

Thanks

Keithc MCSE MCSA

Kristen
Test

22859 Posts

Posted - 2004-06-22 : 13:19:48

Yes.

You could use a


INSERT INTO orders2 
SELECT  I.* 
FROM    inserted I
WHERE NOT EXISTS
(
   SELECT *
   FROM   orders2 O2 
   WHERE  I.orderid = O2.orderid
)

if you prefer

Kristen

keithc1
Yak Posting Veteran

88 Posts

Posted - 2004-06-22 : 13:32:51

That is very cool, it didn't even cross my mind to use a sub query instead of a join.
Thanks for the example

Keithc MCSE MCSA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources