Home » RDBMS Server » Security » Audit Trigger Failure
Audit Trigger Failure [message #23695] Sat, 28 December 2002 07:48 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 #23696 is a reply to message #23695] Sat, 28 December 2002 07:52 Go to previous messageGo to next message
Remi
Messages: 28
Registered: December 2002
Junior Member
Hi

I guess you forgot THEN twice:

ELSE IF UPDATING should by ELSE IF UPDATING THEN

and

ELSE IF DELETING should by ELSE IF DELETING THEN

Good luck !!

http://ora-0000.com/

Re: Audit Trigger Failure [message #23699 is a reply to message #23695] Sat, 28 December 2002 14:54 Go to previous message
CreativePresence
Messages: 73
Registered: December 2002
Member
Cheers for your comments and with those & others the problem has been resolved.

Stuart (UK)
Previous Topic: Audit Trigger Failure
Next Topic: username and password in oracle
Goto Forum:
  


Current Time: Sun May 22 12:50:15 CDT 2022