Re: dbms_metadata for Synonym
Date: 28 Jul 2003 08:38:01 -0700
Message-ID: <3722db.0307280738.109e82b4_at_posting.google.com>
I created a trigger referencing 35 columns, and it worked perfectly. Here's the trigger I used. Please give more advice to be able to duplicate your issue:
CREATE OR REPLACE TRIGGER "SCOTT"."MANY_COLS_CHANGES"
before update on scott.many_cols
for each row
begin
if (:old.col1 != :new.col1) then
insert into scott.many_cols_aud values (user, 'COL1', sysdate);
end if;
if (:old.col2 != :new.col2) then
insert into scott.many_cols_aud values (user, 'COL2', sysdate);
end if;
if (:old.col3 != :new.col3) then
insert into scott.many_cols_aud values (user, 'COL3', sysdate);
end if;
if (:old.col4 != :new.col4) then
insert into scott.many_cols_aud values (user, 'COL4', sysdate);
end if;
if (:old.col5 != :new.col5) then
insert into scott.many_cols_aud values (user, 'COL5', sysdate);
end if;
if (:old.col6 != :new.col6) then
insert into scott.many_cols_aud values (user, 'COL6', sysdate);
end if;
if (:old.col7 != :new.col7) then
insert into scott.many_cols_aud values (user, 'COL7', sysdate);
end if;
if (:old.col8 != :new.col8) then
insert into scott.many_cols_aud values (user, 'COL8', sysdate);
end if;
if (:old.col9 != :new.col9) then
insert into scott.many_cols_aud values (user, 'COL9', sysdate);
end if;
if (:old.col10 != :new.col10) then
insert into scott.many_cols_aud values (user, 'COL10', sysdate);
end if;
if (:old.col11 != :new.col11) then
insert into scott.many_cols_aud values (user, 'COL11', sysdate);
end if;
if (:old.col12 != :new.col12) then
insert into scott.many_cols_aud values (user, 'COL12', sysdate);
end if;
if (:old.col13 != :new.col13) then
insert into scott.many_cols_aud values (user, 'COL13', sysdate);
end if;
if (:old.col14 != :new.col14) then
insert into scott.many_cols_aud values (user, 'COL14', sysdate);
end if;
if (:old.col15 != :new.col15) then
insert into scott.many_cols_aud values (user, 'COL15', sysdate);
end if;
if (:old.col16 != :new.col16) then
insert into scott.many_cols_aud values (user, 'COL16', sysdate);
end if;
if (:old.col17 != :new.col17) then
insert into scott.many_cols_aud values (user, 'COL17', sysdate);
end if;
if (:old.col18 != :new.col18) then
insert into scott.many_cols_aud values (user, 'COL18', sysdate);
end if;
if (:old.col19 != :new.col19) then
insert into scott.many_cols_aud values (user, 'COL19', sysdate);
end if;
if (:old.col20 != :new.col20) then
insert into scott.many_cols_aud values (user, 'COL20', sysdate);
end if;
if (:old.col21 != :new.col21) then
insert into scott.many_cols_aud values (user, 'COL21', sysdate);
end if;
if (:old.col22 != :new.col22) then
insert into scott.many_cols_aud values (user, 'COL22', sysdate);
end if;
if (:old.col23 != :new.col23) then
insert into scott.many_cols_aud values (user, 'COL23', sysdate);
end if;
if (:old.col24 != :new.col24) then
insert into scott.many_cols_aud values (user, 'COL24', sysdate);
end if;
if (:old.col25 != :new.col25) then
insert into scott.many_cols_aud values (user, 'COL25', sysdate);
end if;
if (:old.col26 != :new.col26) then
insert into scott.many_cols_aud values (user, 'COL26', sysdate);
end if;
if (:old.col27 != :new.col27) then
insert into scott.many_cols_aud values (user, 'COL27', sysdate);
end if;
if (:old.col28 != :new.col28) then
insert into scott.many_cols_aud values (user, 'COL28', sysdate);
end if;
if (:old.col29 != :new.col29) then
insert into scott.many_cols_aud values (user, 'COL29', sysdate);
end if;
if (:old.col30 != :new.col30) then
insert into scott.many_cols_aud values (user, 'COL30', sysdate);
end if;
if (:old.col31 != :new.col31) then
insert into scott.many_cols_aud values (user, 'COL31', sysdate);
end if;
if (:old.col32 != :new.col32) then
insert into scott.many_cols_aud values (user, 'COL32', sysdate);
end if;
if (:old.col33 != :new.col33) then
insert into scott.many_cols_aud values (user, 'COL33', sysdate);
end if;
if (:old.col34 != :new.col34) then
insert into scott.many_cols_aud values (user, 'COL34', sysdate);
end if;
if (:old.col35 != :new.col35) then
insert into scott.many_cols_aud values (user, 'COL35', sysdate);
end if;
end;
/
Daniel
> Hi Daniel,
>
> The only common thing among all the incompletely extracted triggers is
> that all of them have large number of trigger columns, approximately
> 25-30 columns.
>
> So all the triggers end like this
>
> column29,
> column30
> on table_name
> referencing
> new as new
> old as old
> for each row
>
> /.
>
> Hope this helps. Let me know if you need any further information.
Received on Mon Jul 28 2003 - 17:38:01 CEST