| :old :new [message #4453] |
Wed, 11 December 2002 08:13  |
Scotty
Messages: 3
Registered: December 2002
|
Junior Member |
|
|
In writing database history I use triggers to record the table, record key, key values, column modified, :old, :new column values, user, time/date stamp, and change type. In this way I can put all inserts, deletes, updates to any table in the database into the same history structure and still track the individual changes. It works well but, I currently individually identify each column name within the particular tables history trigger. Whenever the structure of a table changes the trigger must be modified. I am searching for a method of selecting the table column names dynamically (from the sys.all_tab_columns) and using them in the trigger statement instead of writing individual if statements for each column. I'm having a problem using a variable within the :old.{column}/:new.{column} correlation identifier. Is this even possible and is there a better way to handle it? I have included an example of what I am attempting to do (this example is for updates only):
CREATE OR REPLACE TRIGGER "SCD_DEV"."HU2_CTO" BEFORE UPDATE OF
"CHG_BY",
"CTO_CERT_DATE",
"CTO_CERT_ID",
"CTO_EXPIRATION_DATE",
"CTO_STATUS_CD",
"MAJCOM_CD",
"SERVICE_CD",
"SYSTEM_ID",
"SYSTEM_VERSION"
ON "SCD_DEV"."CTO"
FOR EACH ROW
declare
v_columnname varchar2(50);
cursor c_columnname is
select column_name
from sys.all_tab_columns
where (owner = 'SCD' and table_name = 'ACCOUNT' and column_name Not like 'CHG_%') and (owner = 'SCD' and table_name = 'ACCOUNT' and column_name Not like 'CR_BY%') and (owner = 'SCD' and table_name = 'ACCOUNT' and column_name Not like 'CR_DATE%');
BEGIN
OPEN c_columnname;
LOOP
fetch c_columnname into v_columnname;
exit when c_columnname%notfound;
/* Adds change record to db_history table */
IF NVL(:NEW."v_columnname", '0') != NVL(:OLD."v_columnname", '0') THEN
BEGIN
INSERT INTO db_history (chg_table, chg_key, chg_record, chg_data_element, chg_old, chg_new, chg_by, chg_type)
VALUES ('CTO','SERVICE_CD, MAJCOM_CD, SYSTEM_ID, SYSTEM_VERSION', :NEW.service_cd || :NEW.majcom_cd || :NEW.system_id || :NEW.system_version, "v_columname", :OLD."v_columnname", :NEW."v_columnname", :NEW.CHG_BY,'U');
END;
END IF;
END LOOP;
CLOSE c_columnname;
END hu2_CTO;
|
|
|
|
| Re: :old :new [message #4456 is a reply to message #4453] |
Wed, 11 December 2002 11:19  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Unfortunately you cannot dynamically reference the :old and :new values in that fashion within the trigger. However, what many of us do instead is use the data dictionary to write the trigger for us. This works well if your archive triggers share the same basic template across tables.
If columns are added or dropped from a table, I just run a script that regenerates the trigger by querying the data dictionary and building the trigger PL/SQL.
|
|
|
|
| Re: :old :new [message #4458 is a reply to message #4456] |
Wed, 11 December 2002 11:40 |
Scotty
Messages: 3
Registered: December 2002
|
Junior Member |
|
|
|
Not the reply I was hoping for but it will certainly skin the cat. Thank you Todd.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
