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

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-04-29 : 11:14:42

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

30421 Posts

Posted - 2008-04-29 : 11:20:37

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"

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-04-29 : 15:04:22

peso: thank you. I thought there was something missing :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 02:35:47

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"

squidge08
Starting Member

3 Posts

Posted - 2008-04-30 : 05:14:43

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 05:16:32

My first question is "Are you using Microsoft SQL Server?".

E 12°55'05.25"
N 56°04'39.16"

squidge08
Starting Member

3 Posts

Posted - 2008-04-30 : 05:21:28

we're using SQL 9i

E. Ashworth

squidge08
Starting Member

3 Posts

Posted - 2008-04-30 : 05:22:24

i mean Oracle SQL 9i

E. Ashworth

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 05:33:34

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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources