Home » RDBMS Server » Security » Audit Trigger Failure
Audit Trigger Failure [message #41397] Sat, 28 December 2002 07:49 Go to next message
CreativePresence
Messages: 73
Registered: December 2002
Member
The trigger is finally falling into place & i have managed to iron a few errors out, but still the following trigger is producing 2 errors (see below) & i cant see why?

CREATE OR REPLACE TRIGGER audit_item_values
BEFORE INSERT OR UPDATE OR DELETE ON Item
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO audit_item_table(AUDITDATE, AUDITACTION, USERNAME, Old_QTY, New_Qty, Odl_PRODID, New_ProdID,
Old_ORDID, New_OrdID, Old_ITEMTOT, New_ItemTot, Old_ITEMID, New_ItemID, Old_ACTUALPRICE, New_ActualPrice)
VALUES(Sysdate, "INSERT", USER, :old.Qty, :New.Qty, :old.prodid, :new.prodid,
:old.ordid, :new.ordid, :old.itemtot, :new.itemtot, :old.itemid, :new.itemid,
:old.actualprice, :new.actualprice);

ELSE IF UPDATING
INSERT INTO audit_item_table(AUDITDATE, AUDITACTION, USERNAME, Old_QTY, New_Qty, Odl_PRODID, New_ProdID,
Old_ORDID, New_OrdID, Old_ITEMTOT, New_ItemTot, Old_ITEMID, New_ItemID, Old_ACTUALPRICE, New_ActualPrice)
VALUES(Sysdate, "UPDATE", USER, :old.Qty, :New.Qty, :old.prodid, :new.prodid,
:old.ordid, :new.ordid, :old.itemtot, :new.itemtot, :old.itemid, :new.itemid,
:old.actualprice, :new.actualprice);

ELSE IF DELETING
INSERT INTO audit_item_table(AUDITDATE, AUDITACTION, USERNAME, Old_QTY, New_Qty, Odl_PRODID, New_ProdID,
Old_ORDID, New_OrdID, Old_ITEMTOT, New_ItemTot, Old_ITEMID, New_ItemID, Old_ACTUALPRICE, New_ActualPrice)
VALUES(Sysdate, "DELETE", USER, :old.Qty, :New.Qty, :old.prodid, :new.prodid,
:old.ordid, :new.ordid, :old.itemtot, :new.itemtot, :old.itemid, :new.itemid,
:old.actualprice, :new.actualprice);
END IF;
END IF;
END IF;
End;
/

SHOW ERRORS
/

*********************************************
SQL> @ audittemp

Warning: Trigger created with compilation errors.

Errors for TRIGGER AUDIT_ITEM_VALUES:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/1 PLS-00103: Encountered the symbol "INSERT" when expecting one of
the following:
. ( * @ % & = - + < / > at in is mod not rem then
<an exponent (**)> <> or != or ~= >= <= <> and or like
between ||
The symbol "then" was substituted for "INSERT" to continue.

17/1 PLS-00103: Encountered the symbol "INSERT" when expecting one of
the following:
. ( * @ % & = - + < / > at in is mod not rem then
<an exponent (**)> <> or != or ~= >= <= <> and or like
between ||
The symbol "then" was substituted for "INSERT" to continue.

Warning: Trigger created with compilation errors.
Re: Audit Trigger Failure [message #41399 is a reply to message #41397] Sat, 28 December 2002 12:43 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You were using double quotes instead of single quotes around your audit actions.

However, I would recommend a simpler structure with no redundant code:

create or replace trigger audit_item_values
  before insert or update or delete on item
  for each row
declare
  v_action  audit_item_table.auditaction%type;
begin
  if    inserting then
    v_action := 'INSERT';
  elsif updating then
    v_action := 'UPDATE';
  elsif deleting then
    v_action := 'DELETE';
  end if;
  
  insert 
    into audit_item_table
           (auditdate, auditaction, username, 
            old_qty, new_qty, odl_prodid, new_prodid, old_ordid, new_ordid,
            old_itemtot, new_itemtot, old_itemid, new_itemid, old_actualprice, new_actualprice)
    values
           (sysdate, v_action, user,
            :old.qty, :new.qty, :old.prodid, :new.prodid, :old.ordid, :new.ordid,
            :old.itemtot, :new.itemtot, :old.itemid, :new.itemid, :old.actualprice, :new.actualprice);
end;
/
Re: Audit Trigger Failure [message #41401 is a reply to message #41399] Sat, 28 December 2002 14:52 Go to previous message
CreativePresence
Messages: 73
Registered: December 2002
Member
Thanks for your more slim-line version of the trigger im trying to create. Initially it produced errors, but once intergated into with the table creation process it created without a hitch. So thanks alot for your help.

Stuart (UK)
Previous Topic: Audit Trigger Failure
Next Topic: Audit Trigger Failure
Goto Forum:
  


Current Time: Thu Mar 28 06:33:15 CDT 2024