Note : Not tested
CREATE OR REPLACE TRIGGER ETM_AR_IU AFTER UPDATE ON EXTERNAL_MAPPING REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE V_COL_NAME VARCHAR2(30); PL_SQL_BLOCK VARCHAR2(1000); BEGIN SELECT T.COL_NAME INTO V_COL_NAME FROM TRIGGER_TABS_COLS T; PL_SQL_BLOCK := ' BEGIN IF :OLD.''' || V_COL_NAME || ''' :NEW.''' || V_COL_NAME || ' THEN INSERT INTO hema_clob VALUES (:NEW.'''||V_COL_NAME||''' ); END IF; END;'; EXECUTE IMMEDIATE PL_SQL_BLOCK; END;
3360 wrote:It's obvious isn't it? Having the ability to change the database design without having to change the applications that use the database is the future of generic power applications!!! ;)Hema wrote:Why would you want to do that?
In future, if I want to switch the column name,
Looks to me like a stupid attempt at writing 'generic' code. Any sensible software engineer knows that 'generic' code means 'slow and buggy' code.I have to do without modification in trigger.That is not possible. If you can rename the column why can you not change the trigger at the same time?
and in your trigger, you call the procedure like this
procedure update_table(p_column1_oldval in table_name.column1name%type, p_column1_newval in table_name.column1name%type, p_column2_oldval in table_name.column1name%type, p_column2_newval in table_name.column2name%type ) is begin if p_column1_oldval !=p_column1_newval then -- do something; eg your insert null; -- placeholder remove when you have code end if; if p_column2_oldval !=p_column2_newval then -- do something like your insert null; -- placeholder remove when you have code end if; end update_table;
At least this way, all the work required is done in the procedure , not directly in the trigger.
... for each row begin update_table(p_column1_oldval => :old.column1name, p_column1_newval => :new.column1name, p_column2_oldval => :old.column1name, p_column2_newval=> :new.column1name); end;
Hema wrote:Why does adding a column not require an outage while also changing the trigger code will require an outage.?
If there is addition of column in future then I need to add one more condition like this in trigger. Then we need a outage and promote to apply in live.
I think it's is achievable to make it as a dynamic.It isn't.
I need a code change and outage in future, if I want to add one more column in trigger.Again, why is modifying the code in a trigger to use the added column different from adding the column in the table itself?
I'm trying to think out of boxTry answering the question instead. Why is it not a problem to change the table, but it is a problem to change the trigger on the table?
Hema wrote:How do you "write business logic" for a column that does not yet exist?
Table ABC have columns a, b, and c . For example I wrote my trigger like this only for two column updates ..
CREATE OR REPLACE TRIGGER ABC_AR_U
AFTER UPDATE ON
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
If :old.a <> :new.a then
My business logic
If :old.b <> :new.b then
My business logic again
In near future, if my business says that Implement the same business logic , if there is any update happens on third column c , then I need to implement my trigger with one more if statement . For that I need a code change. To avoid this, I'm trying to implement in a dynamic way by setting a master table.
Hema wrote:Yes. How many new requirements are you planning on implementing without changing the code? The usual way to implement new features, new requirements and changes to requirements in applications is to change the code or write more code.
In near future, if my business says that Implement the same business logic , if there is any update happens on third column c , then I need to implement my trigger with one more if statement . For that I need a code change.
To avoid this, I'm trying to implement in a dynamic way by setting a master table.It is not possible.