I have two tables, Customer and Orders. In the Orders table I have attributes or

Question

I have two tables, Customer and Orders. In the Orders table I have attributes orderkey, customerkey, orderstatus, and totalprice. I am trying to create a trigger where if I try to insert a new order for a customer who has 13 open orders then it will reject the insert. So pretty much a customer cannot have more than 13 open orders. In the orderstatus attribute you have 'o' for open and 'c' for closed. There is also a foreign key customerkey references customerkey in Customer table. I was wondering if someone can help me write the trigger in postgresql including the function.

Explanation / Answer

CREATE TRIGGER Order_Limit ON Orders

FOR INSERT

AS

declare @orderkey int;

declare @custkey int;

declare @orderstat char;

declare @total decimal(100,2);

declare @totorders int;

select @orderkey=i.orderkey from inserted i;

select @custkey=i.customerkey from inserted i;

select @orderstat=i.orderstatus from inserted i;

select @total=i.totalprice from inserted i;

select @totorders=count(@custkey) from Orders;

if(@totorders>12)

update(Orders)

set orderstatus='c' where customerkey=@custkey

PRINT 'INSERT UNSUCCESSFUL'

else

INSERT INTO ORDERS(orderkey,customerkey,orderstatus,totalprice)

values(@orderkey,@custkey,'o',@total)

PRINT 'INSERT SUCCESSFUL'

GO

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects