I'm writing a database trigger for a history function.
I had the hope that there is a chance to use one trigger for multiple tables. Also I don't want to check every single column by name.
So I tried to create the trigger using dynamical SQL like this:
FOR columnItem IN (SELECT column_name
FROM all_tab_columns
WHERE TABLE_NAME = UPPER(tableName))
LOOP
-- how to use columnItem.column_name for the loop
-- like :new.[columnItem.column_name] or something like that
if :new.NAME != :old.NAME Then
INSERT INTO HISTORY
VALUES (HIS_SEQUENCE.nextval, tableName, :new.LOCAL_ID, columnItem.column_name, 'OLD', 'NEW', sysdate(), 'STACKOVERFLOW');
End If;
END LOOP;
That's not possible, is it? Hope you know a way to do it.
Thanks in advance.