Re: Trigger question..please help.

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Mon, 30 Mar 2009 17:06:48 +0200
Message-ID: <49d0e005$0$201$e4fe514c_at_news.xs4all.nl>

Michael Austin schreef:
> pereges wrote:

>> On Mar 27, 9:34 pm, pereges <Brol..._at_gmail.com> wrote:
>>> On Mar 27, 8:43 pm, Shakespeare <what..._at_xs4all.nl> wrote:> By the 
>>> dno column in employees I would think.... it may be a bit more
>>>> complex than I stated, but it can still be done this way. I just gave
>>>> some direction.
>>> Ok but how do I reference the tuple which has been updated from inside
>>> the trigger ?
>>
>> What I mean to say is that you cannot use OLD and NEW inside a
>> statement level trigger. So how to access that data ?

>
> Sounds like you have a long way to go in your learning curve...
>
> Can you explain what you mean by "statement level trigger"??
>
> http://www.adp-gmbh.ch/ora/sql/trigger/new_old.html ????
>
>
> CREATE OR REPLACE TRIGGER UpdateDeptStatSL
> AFTER INSERT OR UPDATE OR DELETE ON employee
> for each row
> DECLARE
> v_action varchar2 (20);
> BEGIN
> IF INSERTING THEN
> v_action := 'Added employee(s)';
> INSERT INTO DEPTSTAT
> values (:new.a,:new.b,:new.c...)
> ELSIF UPDATING THEN
> v_action := 'Updated employee(s)';
> INSERT INTO DEPTSTAT
> values (:new.a,:new.b,:new.c,
> :old.d,:old.e,:old.f...)
> ELSIF DELETING THEN
> v_action := 'Deleted employee(s)';
> INSERT INTO DEPTSTAT
> values (:old.a,:old.b,:old.c...)
> END IF;
> INSERT INTO statement_level_log VALUES (SYSDATE, USER,
> v_action);
> -- not sure what you need to insert into DeptStat table..
> END;
>
> You can go to all of this trouble - OR - you can investigate Fine
> Grained Auditing...

I see the problem now: I missed the 'statement' level part. _at_Michael: it's the table level trigger in stead of the row level trigger, firing once for the total transaction (so not 'for each row'). You will indeed have to query the tables to update your balances.... In 11G you could have used a compound trigger.

Shakespeare Received on Mon Mar 30 2009 - 10:06:48 CDT