Re: New to Triggers, need a Delete Trig

From: Harald Henkel <Harald_at_Henkel.DAH.UUnet.DE>
Date: Mon, 12 Feb 2001 00:30:44 +0100
Message-ID: <9677e5$jog85$1@ID-56141.news.dfncis.de>

<pk1023_at_my-deja.com> schrieb im Newsbeitrag news:962ctu$8fe$1_at_nnrp1.deja.com...
>
>
> Hello All,
>
> I am very new to the Oracle enivorment, but I need to create a trigger
> I think.
>
> I have a table of workorders for my plant, the information about the
> build of the job is placed into the records as the job progresses
> through the plant. Then at the time of ship the records are deleted and
> made into a flat text file for a mainframe.
>
> Now lots of people are saying that I am loosing that flat file, so I
> need a record of the workorders, but I don't want them hanging around
> in my main table, because there is alot of data assoicated that I will
> not need further and people would get confused on the web pages.
>
> What I need is this...
>
> On delete from workorders
>
> Copy
> workorders.id, .job_order, .line, .line_set_number, .date_requried, .ESC
> _serial, .version, .date_of_test values ('xg######', etc...) to
> workorders_audit
>
> I would like it to fire everytime there is a delete, this way I know
> everything is done,because only shipping can delete workorder records.
>
> Could somebody help me turn this into a trigger for the workorders
> table? Thank you so much.

How about this ?

CREATE OR REPLACE TRIGGER tr_workorders_del

   BEFORE DELETE ON workorders
   FOR EACH ROW
BEGIN
  INSERT
    INTO workorders_audit
  VALUES

       (
         OLD.id,
         OLD.job_order,
         OLD.line,
         OLD.line_set_number,
         OLD.date_requried,
         OLD.ESC_serial,
         OLD.version,
         OLD.date_of_test
       )

  ;
END ;
/

I hope this may help.

With kind regards,
Harald Henkel Received on Sun Feb 11 2001 - 17:30:44 CST