I need to create a table that audits the changes to a table which only has 2 columns (name and system) I need to keep track of transaction date, user performing the transaction, transaction type, old value, new value, table name. This is my code so far. When I run the trigger to add the old value before it updates the error I am getting is Error(3,2): PL/SQL: SQL Statement ignored Error(4,3): PL/SQL: ORA-00928: missing SELECT keyword and the error I'm getting when I run the trigger that updates the rest of the columns is: Error(7,2): PLS-00103: Encountered the symbol "FOR" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior
--create table DROP TABLE DBMonitor CASCADE CONSTRAINTS; CREATE TABLE DBMonitor (TABLE_NAME VARCHAR2(30), TRANS_NAME CHAR(1), TRANS_USER VARCHAR2(30), TRANS_DATE DATE, OLD_VALUE VARCHAR2(100), NEW_VALUE VARCHAR2(100));
/* FOR EACH ROW TRIGGER WHEN CLAUSE (note no : before the OLD) :OLD PSEUDORECORD */ CREATE OR REPLACE TRIGGER RIVERCHANGE BEFORE UPDATE ON TBLRIVER2 FOR EACH ROW WHEN (OLD.NAME IS NOT NULL) BEGIN -- insert record into audit table for each row whose grade is changed INSERT INTO DBMonitor(OLD_VALUE) (':OLD.NAME || ' ' || :OLD.SYSTEM)'; END;
--Trigger CREATE OR REPLACE TRIGGER AuditTrigger AFTER INSERT OR DELETE OR UPDATE ON tblriver2
DECLARE
v_statement DBMonitor.Trans_Name%TYPE; v_tablename DBMonitor.Table_Name%TYPE := 'CATEGORY'; v_user DBMonitor.Trans_User%TYPE := USER; v_Date DBMonitor.Trans_Date%TYPE := SYSDATE; newval VARCHAR2(100); FOR EACH ROW WHEN (OLD.NAME IS NOT NULL) BEGIN
-- checks to see which statement type caused the trigger to fire IF INSERTING THEN v_statement := 'I'; ELSIF UPDATING THEN v_statement := 'U'; ELSE v_statement := 'D'; END IF; NEWval :=
NEW.NAME || ' ' || :NEW.SYSTEM); INSERT INTO DBMonitor VALUES (v_tablename, v_statement, v_user, v_date); END AuditTrigger;
أكثر...
--create table DROP TABLE DBMonitor CASCADE CONSTRAINTS; CREATE TABLE DBMonitor (TABLE_NAME VARCHAR2(30), TRANS_NAME CHAR(1), TRANS_USER VARCHAR2(30), TRANS_DATE DATE, OLD_VALUE VARCHAR2(100), NEW_VALUE VARCHAR2(100));
/* FOR EACH ROW TRIGGER WHEN CLAUSE (note no : before the OLD) :OLD PSEUDORECORD */ CREATE OR REPLACE TRIGGER RIVERCHANGE BEFORE UPDATE ON TBLRIVER2 FOR EACH ROW WHEN (OLD.NAME IS NOT NULL) BEGIN -- insert record into audit table for each row whose grade is changed INSERT INTO DBMonitor(OLD_VALUE) (':OLD.NAME || ' ' || :OLD.SYSTEM)'; END;
--Trigger CREATE OR REPLACE TRIGGER AuditTrigger AFTER INSERT OR DELETE OR UPDATE ON tblriver2
DECLARE
v_statement DBMonitor.Trans_Name%TYPE; v_tablename DBMonitor.Table_Name%TYPE := 'CATEGORY'; v_user DBMonitor.Trans_User%TYPE := USER; v_Date DBMonitor.Trans_Date%TYPE := SYSDATE; newval VARCHAR2(100); FOR EACH ROW WHEN (OLD.NAME IS NOT NULL) BEGIN
-- checks to see which statement type caused the trigger to fire IF INSERTING THEN v_statement := 'I'; ELSIF UPDATING THEN v_statement := 'U'; ELSE v_statement := 'D'; END IF; NEWval :=
NEW.NAME || ' ' || :NEW.SYSTEM); INSERT INTO DBMonitor VALUES (v_tablename, v_statement, v_user, v_date); END AuditTrigger; أكثر...