Home » SQL & PL/SQL » SQL & PL/SQL » Trigger issues using execute immediate
| Trigger issues using execute immediate [message #133882] |
Mon, 22 August 2005 10:34  |
thebat
Messages: 3
Registered: August 2005
|
Junior Member |
|
|
I've been attempting to create a field audit for a list of tables and fields. What I've come up with so far is a table that I use for the table and field names (tbl_Audit_Fields) of what I want to log. I placed an after update trigger on this table that takes the :NEW.TABLE_NAME from the incoming data and passes it to a procedure that then selects all of the records for that table and dynamically builds a trigger for that table that includes all of the fields that I want to audit.
Example:
Record for tbl_Audit_Fields
tbl_my_table fld_my_field
When this record is committed to the tbl_Audit_Fields table a trigger is executed that calls gen_audit_trigger passing in the value of tbl_my_table. The procedure then selects all of the records that are in tbl_Audit_Fields to see if there are any other fields that I want to audit in tbl_my_table. Then I build dynamically build a new trigger for tbl_my_table from that list of fields and do an execute immediate to execute the new trigger code.
What I'm getting is an error ORA-04092: cannot COMMIT or ROLLBACK in a trigger. Is there any way to execute the new trigger that I've created from inside another trigger?
Thanks, Curt
|
|
|
|
|
|
| Re: Trigger issues using execute immediate [message #133896 is a reply to message #133886] |
Mon, 22 August 2005 11:49  |
thebat
Messages: 3
Registered: August 2005
|
Junior Member |
|
|
I'm using it for an application. I wanted an easy way to audit a few tables that I could code a front end around fairly easy. I don't know that much about the build in auditing but from what I understand it's not easy to get the audit trail to display in an users application with out a great deal of work.
|
|
|
|
|
|
| Re: Trigger issues using execute immediate [message #134127 is a reply to message #133982] |
Tue, 23 August 2005 11:09 |
thebat
Messages: 3
Registered: August 2005
|
Junior Member |
|
|
I tried putting the "pragma autonomous_transaction;" statement in the trigger as you suggested but I got the same error unable to perform commit in a trigger. If I move it to the procedure it runs and executes but doesn't appear to create the cursor that I'm using to get the individual field values.
Thanks, Curt
*******
Below is the trigger that I'm trying to get working.
*******
CREATE OR REPLACE TRIGGER EN_AUDIT_FIELD_AITRG
BEFORE INSERT
ON EN_AUDIT_FIELD
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
tmpVar := 0;
if :NEW.EN_AUDIT_FIELD_SID is null then
Select EN_AUDIT_FIELD_SEQ.NextVal into tmpVar from dual;
:NEW.EN_AUDIT_FIELD_SID:= tmpVar;
end if;
CHAMPS_CUSTOM.GEN_AUDIT_TRIGGER(:NEW.TABLE_NAME);
END;
*****
This is the procedure that I'm trying to call.
*****
PROCEDURE GEN_AUDIT_TRIGGER
(pTable_Name IN VARCHAR2)
IS
pragma autonomous_transaction;
v_Trigger_String varchar2(3000);
v_Field_Name varchar2(30);
v_Table_Name varchar2(30);
v_tmp varchar2(100);
CURSOR AL_CURSOR is
select * from EN_AUDIT_FIELD_TRGLIST where table_name = pTable_Name;
AL_FIELDS AL_CURSOR%ROWTYPE;
BEGIN
v_Table_Name := pTable_Name;
v_Trigger_String := 'CREATE OR REPLACE TRIGGER audit_' || v_Table_Name || '_trg' || CHR(10);
v_Trigger_String := v_Trigger_String || 'after insert or update' || CHR(10);
v_Trigger_String := v_Trigger_String || ' on ' || v_Table_Name || CHR(10);
v_Trigger_String := v_Trigger_String || 'for each row' || CHR(10) || CHR(10);
v_Trigger_String := v_Trigger_String || 'DECLARE' || CHR(10);
v_Trigger_String := v_Trigger_String || 'NEW_EN_AUDIT_LOG_SEQ EN_AUDIT_LOG.EN_AUDIT_LOG_SID%TYPE;' || CHR(10);
v_Trigger_String := v_Trigger_String || CHR(10) ||'Begin ' || CHR(10);
FOR AL_FIELDS IN AL_CURSOR
LOOP
v_Field_Name := AL_FIELDS.FIELD_NAME;
v_Trigger_String := v_Trigger_String || ' IF (:OLD.';
v_Trigger_String := v_Trigger_String || v_Field_Name || ' != :NEW.' || v_Field_Name || ') THEN' || CHR(10) || CHR(10);
v_Trigger_String := v_Trigger_String || ' SELECT EN_AUDIT_LOG_SEQ.NextVal INTO NEW_EN_AUDIT_LOG_SEQ FROM DUAL;' || CHR(10) || CHR(10);
v_Trigger_String := v_Trigger_String || ' insert into EN_AUDIT_LOG' || CHR(10);
v_Trigger_String := v_Trigger_String || ' (EN_AUDIT_LOG_SID,' || CHR(10);
v_Trigger_String := v_Trigger_String || ' AUDIT_FIELD_NAME,' || CHR(10);
v_Trigger_String := v_Trigger_String || ' AUDIT_TABLE_NAME,' || CHR(10);
v_Trigger_String := v_Trigger_String || ' OLD_VALUE,' || CHR(10);
v_Trigger_String := v_Trigger_String || ' NEW_VALUE,' || CHR(10);
v_Trigger_String := v_Trigger_String || ' RECORD_CHANGED_BY,' || CHR(10);
v_Trigger_String := v_Trigger_String || ' RECORD_CHANGE_DATE,' || CHR(10);
v_Trigger_String := v_Trigger_String || ' RECORD_SID)' || CHR(10);
v_Trigger_String := v_Trigger_String || ' values( ' || CHR(10);
v_Trigger_String := v_Trigger_String || ' NEW_EN_AUDIT_LOG_SEQ,' || CHR(10);
v_Trigger_String := v_Trigger_String || ' ''' || v_Field_Name || ''',' || CHR(10);
v_Trigger_String := v_Trigger_String || ' ''' || v_Table_Name || ''',' || CHR(10);
v_Trigger_String := v_Trigger_String || ' :OLD.'|| v_Field_Name ||',' || CHR(10);
v_Trigger_String := v_Trigger_String || ' :NEW.' || v_Field_Name ||',' || CHR(10);
v_Trigger_String := v_Trigger_String || ' :NEW.CHANGED_BY,' || CHR(10);
v_Trigger_String := v_Trigger_String || ' sysdate,' || CHR(10);
v_Trigger_String := v_Trigger_String || ' :OLD.EN_TEST_SID);' || CHR(10);
v_Trigger_String := v_Trigger_String || ' END IF;' || CHR(10) || CHR(10);
END LOOP;
v_Trigger_String := v_Trigger_String || 'END audit_' || v_Table_Name || '_trg;';
insert into CXK_TRG_CODE (Text) values(v_Trigger_String);
execute immediate(v_Trigger_String);
END GEN_AUDIT_TRIGGER;
|
|
|
|
Goto Forum:
Current Time: Sun May 18 22:15:37 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
