| Trigger [message #190290] |
Wed, 30 August 2006 01:23  |
eqqqqqq
Messages: 30
Registered: August 2006
|
Member |
|
|
Hi !
I wonder how to create a trigger, which changes the value of several columns...
create or replace trigger TRG1
after insert or update on Item_of_invoice
FOR EACH ROW
begin
if update BOOK set IN_STOCK = IN_STOCK - :NEW.QUANTITY
where book.isbn = :NEW.isbn;
update INVOICE set amount = (quantity * book.price) +:NEW.amount
where INVOICE.invoice_nr = :NEW.invoice_nr;
END;
|
|
|
|
|
|
| Re: Trigger [message #190440 is a reply to message #190292] |
Wed, 30 August 2006 14:21  |
eqqqqqq
Messages: 30
Registered: August 2006
|
Member |
|
|
yes, If I change values in table Item_of_invoice, it should trigger
changes of column IN_STOCK(table book) and AMOUNT (table INVOICE)
... the amount=(Item_of_invoice.quantity * book.price)
create or replace trigger TRG1
after insert or update on Item_of_invoice
FOR EACH ROW
begin
if update BOOK set IN_STOCK = IN_STOCK - :NEW.QUANTITY
where book.isbn = :NEW.isbn;
update INVOICE set amount = (Item_of_invoice.quantity * book.price) +:NEW.amount
where INVOICE.invoice_nr = :NEW.invoice_nr;
END;
|
|
|
|
| Re: Trigger [message #190489 is a reply to message #190440] |
Thu, 31 August 2006 00:50 |
aorehek
Messages: 52
Registered: August 2006
|
Member |
|
|
here is an example...
CREATE OR REPLACE TRIGGER TRG1
AFTER INSERT OR UPDATE ON Item_of_invoice
FOR EACH ROW
BEGIN
IF UPDATING THEN
UPDATE BOOK SET IN_STOCK = IN_STOCK - :NEW.QUANTITY
WHERE book.isbn = :NEW.isbn;
UPDATE INVOICE
SET amount = (Item_of_invoice.quantity * book.price)
+:NEW.amount
WHERE INVOICE.invoice_nr = :NEW.invoice_nr;
END IF;
IF INSERTING THEN
...
....
.....
END IF;
IF UPDATING('QUANTITY') THEN
..
...
....
END IF;
END;
[Updated on: Thu, 31 August 2006 00:50] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
