Want to write a trigger like below. Is it possible? Definitely it is compiled with errors.
CREATE OR REPLACE TRIGGER SCOTT.T_TRG BEFORE INSERT ON SCOTT.T
FOR EACH ROW
begin
for i in ( select column_name from all_tab_columns where owner = 'SCOTT' and table_name = 'T')
loop
if :new.i is null then
:new.i := 'N';
end if;
end loop;
END;
/
The reason to use the variable i is there are many columns in T in real situation, so if writing the 'if' block, that would be too many. Take an example, we can assume there are 3 columns in T like below
create table scott.T( c1 varchar2(30), c2 varchar2(10), c3 varchar2(20)); How to use the variable in this case?
Can any gurus shed some light on it?
insert into t values(null,null);