Home » RDBMS Server » Security » Audit Trigger Failure
Audit Trigger Failure [message #4576] Mon, 23 December 2002 16:47 Go to next message
CreativePresence
Messages: 73
Registered: December 2002
Member
I have this script the first creats an audit table, then an audit trigger & then the query that allows the user to insert data into a table & the audit trigger is keep a record of the data inserted & puts it into the audit table, but first i get a message that the trigger was created with compilation errors & then once data has been inserted into the table i get the message "ora-04098: trigger 'scott.t_audit' is invalid and failed re-validation. Why?

Set Echo Off
Set Linesize 150
Set Pagesize 150
Set Verify Off

DROP TABLE AUDIT_ITEM_TABLE;
DROP TRIGGER audit_item_values;

CREATE TABLE AUDIT_ITEM_TABLE (
ORDID NUMBER (4),
ITEMID NUMBER (4),
PRODID NUMBER (6),
ACTUALPRICE NUMBER (8,2),
QTY NUMBER (8),
ITEMTOT NUMBER (8,2));

CREATE OR REPLACE TRIGGER audit_item_values
AFTER DELETE OR INSERT OR UPDATE ON Item
FOR EACH ROW
BEGIN
INSERT INTO audit_item_table(QTY, PRODID, ORDID, ITEMTOT, ITEMID, ACTUALPRICE)
VALUES(: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;
/

Column "Customer Name" Format a20
PROMPT
PROMPT * * * * * * * * * * * * * * * ITEM INSERT SCREEN * * * * * * * * * * * * * * *
PROMPT
PROMPT Please enter a valid Order ID or order date (DD-MON-YY) that you
PROMPT wish to view the details off or type "ALL" to view all order details.
PROMPT
ACCEPT Item_OrdID CHAR -
PROMPT 'Please enter your choice: '

Select
Ord.OrdID AS "Order ID",
Item.ItemID AS "Item ID",
Item.ProdID AS "Product ID",
Cust.Name AS "Customer Name",
Item.ActualPrice AS "Product Price",
Item.Qty AS "Quantity",
Item.ItemTot AS "Total Item Cost",
Ord.Total AS "Total Order Cost"
FROM Ord Ord, Item Item, Customer Cust
WHERE (UPPER ('&Item_OrdID') = 'ALL'
OR INSTR ('&Item_OrdID', TO_CHAR (Ord.OrdID)) > 0
OR UPPER ('&Item_OrdID') = TO_CHAR (Ord.OrderDate, 'DD-MON-YY'))
And Ord.OrdID = Item.OrdID
And Ord.CustID = Cust.CustID
Order by Ord.OrdID, Item.ItemID
/

PROMPT
Accept Item_OrdID PROMPT 'Enter the "Order ID" you wish to add an item to: '
Accept Item_ItemID PROMPT 'Enter the "Item ID" you wish to add to the order: '
Accept Item_ProdID PROMPT 'Enter the "Product ID" number you wish to add to the order : '
Accept Item_ActualPrice PROMPT 'Enter the "Price" of the item you wish to add to the order: '
Accept Item_Qty PROMPT 'Enter the "Quantity" of the item you wish to add to the order: '
Accept Item_ItemTot PROMPT 'Enter the "Items Total" you wish to add to the order: '

INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE)
VALUES (&Item_Qty, &Item_ProdID, &Item_OrdID, &Item_ItemTot, &Item_ItemID, &Item_ActualPrice);
Re: Audit Trigger Failure [message #4577 is a reply to message #4576] Mon, 23 December 2002 18:07 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Please see reply in PL/SQL forum.
Previous Topic: Audit Trigger Failure
Next Topic: Audit Trail
Goto Forum:
  


Current Time: Fri Dec 09 04:18:49 CST 2022