trigger question #2 - 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 question #2

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-04-30 : 08:55:03

i have following two tables:
table orders (id_order, order_date, customer_id)
table orders_line (id_order, id_order_line, id_product, product_name)
this two tables are filled automatically.

and a third destination table as a backup table:
table backup_order (id_order, order_date, id_order_line, id_product)
this one is going to be filled by trigger.

following will insert:
----
create trigger orders_product on orders_line
after insert
AS

insert into
backup_order (id_order, order_date, id_order_line, id_product)

select o.id_order,o.order_date,ol.id_order_line ,ol.id_product
from orders as o
join orders_line as ol on o.id_order = ol.id_order
join inserted as i on i.id_order_line = ol.id_order_line
where ol.id_product = 'ABC123Product']
----

now i need also a trigger
that will make insert and update on existing table:
---
CREATE trigger order_products on orders_line
for insert, update
as
begin
update backup_order
set id_product = ol.id_product

from
orders_line as ol
join inserted as i on i.id_order_line = ol.id_order_line
where
i.id_order_line = ol.id_order_line
end
----
but it does not update new cell with new data.

any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-30 : 08:58:26

Do you need seperate triggers? cant you include the code in single trigger itself? Also where is backup_order linked to other two tables data in update?

X002548
Not Just a Number

15586 Posts

Posted - 2008-04-30 : 08:59:03

Not pretending to have read the post

But what exactly do you want to do?

I don't think there is a reason for an INSTEAD of trigger

And what about DELETE?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-04-30 : 09:39:25

visakh16: i'm making two triggers each for it's table (orders, orders_line).

Idea is to have both triggered when new line/update occurs and all info should be gathers in backup_order. that's it. :)

X002548
Not Just a Number

15586 Posts

Posted - 2008-04-30 : 09:56:27

Basic concept

New Insert are in the base table

Modified data (deletes, updates) get written to history

Is that what you want to do?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-30 : 10:32:37

quote:
Originally posted by visakh16

Do you need seperate triggers? cant you include the code in single trigger itself? Also where is backup_order linked to other two tables data in update?

As per code you posted you are creating both the triggers on same table orders_line

X002548
Not Just a Number

15586 Posts

Posted - 2008-04-30 : 10:36:05

Here's another concept

Triggers "Fire" related to a DML event

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources