Home » RDBMS Server » Security » Audit Trigger Failure
Audit Trigger Failure [message #41397] |
Sat, 28 December 2002 07:49 |
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 |
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 |
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)
|
|
|
Goto Forum:
Current Time: Fri Sep 27 20:18:30 CDT 2024
|