Home » SQL & PL/SQL » SQL & PL/SQL » Trigger
Trigger [message #190290] Wed, 30 August 2006 01:23 Go to next message
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 #190292 is a reply to message #190290] Wed, 30 August 2006 01:40 Go to previous messageGo to next message
aorehek
Messages: 52
Registered: August 2006
Member
Which columns in which tables do you want to change ?
Re: Trigger [message #190440 is a reply to message #190292] Wed, 30 August 2006 14:21 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

Re: Trigger [message #190667 is a reply to message #190489] Thu, 31 August 2006 11:56 Go to previous message
eqqqqqq
Messages: 30
Registered: August 2006
Member
Thank you very much! =)
Previous Topic: Adding millions of rows to a new table in 11i with unique index
Next Topic: Archiving of Table data
Goto Forum:
  


Current Time: Wed Dec 07 07:11:04 CST 2016

Total time taken to generate the page: 0.15323 seconds