CODEWITHSUNDEEP
sqloracle-databasetriggersplsql
Mohit
Mohit

Reputation: 11324

Trigger to update values

I want to create a trigger on my table Audit the function of trigger is : it will set all values to NULL which have string 'null' means 'null' -> NULL how should i do it i want it for every column not any specific.

Upvotes: 0

Views: 93

Answers (1)

APC
APC

Reputation: 146349

There's no reflection in PL/SQL so you'll need to do it like this:

create or replace trigger aud_upd_biur
before insert or update on your_audit_table
for each row
begin
     if :new.col1 = 'null'
     then
         :new.col1 := null;
     end if;
     if :new.col2 = 'null'
     then
         :new.col2 := null;
     end if;
     ....
     if :new.col99 = 'null'
     then
         :new.col99 := null;
     end if;
end;

So, if your audit table has only a few columns cut'n'paste will suffice. Otherwise you can generate the code out of the data dictionary.

Upvotes: 3

Related Questions