I've built an update row trigger on several tables. The purpose is to capture the old and new values in variables then passing these values to a package containing MANY functions/procedures that validate the data. Each table is structured differently. I've got about 183 tables that need to have this trigger applied to it.
Anyway, the workaround I've found for the first few tables is to overload the procedures in the package and pass in the literal table_name%rowtype (ex. emp%rowtype). This is at best a cumbersome solution because of the number of tables that will eventually be passed.
I've tried the following:
1) dynamically building the %rowtype variable then passing that variable (on compile I get an error saying table_name must be a table)
2) creating a global temporary table in a separate procedure using pragma autonomous transaction (when I run the update I get an ORA-03031 error because the user has insufficient priveliges. We want to assign priveliges by roles not by individuals, so this option doesn't work for us.)
3) create a cursor by inserting the new/old values into the cursor. (seems that we have to do a select statement to create the cursor then fetch records from the cursor into a variable. We really just want the table structure, not the data. Then we want to insert the values from the
ld & :new values).
4) Our DBA doesn't like varrays, nested tables, or object types so that doesn't seem to be an option.
I know these triggers must be done all of the time, but I can't figure out how to do it.
tia, robin
Anyway, the workaround I've found for the first few tables is to overload the procedures in the package and pass in the literal table_name%rowtype (ex. emp%rowtype). This is at best a cumbersome solution because of the number of tables that will eventually be passed.
I've tried the following:
1) dynamically building the %rowtype variable then passing that variable (on compile I get an error saying table_name must be a table)
2) creating a global temporary table in a separate procedure using pragma autonomous transaction (when I run the update I get an ORA-03031 error because the user has insufficient priveliges. We want to assign priveliges by roles not by individuals, so this option doesn't work for us.)
3) create a cursor by inserting the new/old values into the cursor. (seems that we have to do a select statement to create the cursor then fetch records from the cursor into a variable. We really just want the table structure, not the data. Then we want to insert the values from the
ld & :new values). 4) Our DBA doesn't like varrays, nested tables, or object types so that doesn't seem to be an option.
I know these triggers must be done all of the time, but I can't figure out how to do it.
tia, robin