SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 trigger -question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts

Posted - 04/29/2008 :  11:14:42  Show Profile  Reply with Quote
hi,

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.

and following trigger won't do 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

		where
			ol.id_product = 'ABC123Product'
	END


what shall i change in order to get data into table backup_order whenever there is a new order / order_line insert with this product.

thank you.

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 04/29/2008 :  11:20:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You have to join the inserted virtual table to only touch those records affected by the insert.
otherwise you will get all records in table.
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']



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

slimt_slimt
Aged Yak Warrior

Switzerland
746 Posts

Posted - 04/29/2008 :  15:04:22  Show Profile  Reply with Quote
peso: thank you. I thought there was something missing :)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 04/30/2008 :  02:35:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Still missing for which order the orderline is to be "backuped".

join inserted as i on i.id_order_line = ol.id_order_line AND i.id_order = ol.id_order

Otherwise you get orderline for all orders.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

squidge08
Starting Member

United Kingdom
3 Posts

Posted - 04/30/2008 :  05:14:43  Show Profile  Click to see squidge08's MSN Messenger address  Reply with Quote
Hi i am also struggling with a trigger. i have a statement to which i ahve to create the SQL for the answer. the statement is:
A member can have on loan a maximum of 5 items at any one time.

The that is mainly included in this is:
CREATE TABLE hire(
hireID INTEGER PRIMARY KEY,
memberID INTEGER NOT NULL,
stockID INTEGER NOT NULL,
staffID INTEGER NOT NULL,
date_hired DATE NOT NULL,
date_due DATE NOT NULL,
date_return DATE,
amount_paid VARCHAR2(6) NOT NULL,
CONSTRAINT fk_mID FOREIGN KEY (memberID) references member(memberID),
CONSTRAINT fk_hirestkID FOREIGN KEY (stockID) references stock(stockID),
CONSTRAINT fk_hirestfID FOREIGN KEY (staffID) references staff(staffID));


My trigger so far is:
CREATE OR REPLACE TRIGGER
triggerb1
BEFORE INSERT
ON hire
FOR EACH ROW
declare
memberID;
BEGIN
SELECT member.memberID, COUNT (*)
FROM hire
WHERE member.memberID ='&memberID'
AND hire.date_return is null
GROUP BY (memberID)
IF (dummy < 5)
THEN
INSERT INTO hire
BEGIN
PRINT ''
ELSE
PRINT 'Member has too many items on loan.'
END;
/

I can't get it to work & i don't know if it is even right.
Please can you help me
Emma

E. Ashworth
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 04/30/2008 :  05:16:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
My first question is "Are you using Microsoft SQL Server?".



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

squidge08
Starting Member

United Kingdom
3 Posts

Posted - 04/30/2008 :  05:21:28  Show Profile  Click to see squidge08's MSN Messenger address  Reply with Quote
we're using SQL 9i

E. Ashworth
Go to Top of Page

squidge08
Starting Member

United Kingdom
3 Posts

Posted - 04/30/2008 :  05:22:24  Show Profile  Click to see squidge08's MSN Messenger address  Reply with Quote
i mean Oracle SQL 9i

E. Ashworth
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 04/30/2008 :  05:33:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I think you should turn to www.dbforums.com since this is a Microsoft SQL Server forum.

But you don't need a before (instead of) trigger here.
CREATE TRIGGER triggerb1 ON hire
AFTER INSERT, UPDATE
AS

SET NOCOUNT ON

IF EXISTS	(
			SELECT		h.memberID
			FROM		hire AS h
			INNER JOIN	inserted AS i ON i.memberID = h.memberID
			WHERE		h.date_return IS NULL
			GROUP BY	h.memberID
			HAVING		COUNT(*) > 5
		)
	BEGIN
		ROLLBACK TRAN
		RAISERROR('Member has too many items on loan.', 16, 1)
	END



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000