hi everyone,
I've a trouble because I'm trying detect in a trigger if a nested table has been changed or not. I'm using the function UPDATING([column_of_name]) but doesn't work with this column type. Only I want detect that the column has been changed, I don't want to know what data have been changed.
Is this possible?
This is the trigger:
create or replace TRIGGER NESTE_TR
BEFORE INSERT OR UPDATE
ON TEST_TABLE FOR EACH ROW
BEGIN
IF inserting THEN
DBMS_OUTPUT.PUT_LINE('INSERTING');
END IF;
IF updating THEN
IF updating('NESTED_COLUMN') THEN
DBMS_OUTPUT.PUT_LINE('UPDATING');
END IF;
END IF;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR');
END;
And this is the neste column
user type definition
-----------------------------------------------------------------------
TYPE NESTED_TYPE as object
(
ID NUMBER(10) , COL_1 TIMESTAMP(8) , COL_2 VARCHAR2(4000)
) NOT FINAL
Thanks
The example below loops through all the columns of a table and passes them to the updating function. As you can see, this doesn't work for the nested table:
create or replace type nt is table of varchar2(30);
/
create table t (
id int, nested_col nt
) nested table nested_col store as nested_col_tab;
insert into t values (1, nt('a'));
create or replace trigger nested_tr
before update on t
for each row
begin
if updating then
for r in (
select column_name from all_tab_columns
where table_name = 'T'
and owner = 'CHRIS'
) loop
if updating(r.column_name) then
dbms_output.put_line('Updated ' || r.column_name );
else
dbms_output.put_line('NOT FOR ' || r.column_name );
end if;
end loop;
end if;
end;
/
update t
set id = 1, nested_col = nt('b');
BUT NOT NESTED_COL (apparently...)
UPDATING ID
1 row updated.So how do you detect this?
By comparing the new and old values!
First check if the counts are different. If they are, you know it's updated. If they're the same you need to loop through all the elements to see if any have different values:
create or replace trigger nested_tr
before update on t
for each row
begin
if updating then
if :new.nested_col.count <> :old.nested_col.count then
dbms_output.put_line('Changed number of elements');
else
for i in 1 .. :new.nested_col.count loop
if :new.nested_col(i) <> :old.nested_col(i) then
dbms_output.put_line('Changed element ' || i);
end if;
end loop;
end if;
end if;
end;
/
update t
set id = 1, nested_col = nt('c');
Changed element 1
1 row updated.
update t
set id = 1, nested_col = nt('c', 'd');
Changed number of elements
1 row updated.If you have a nested table of an object, inspect each attribute of it when looping through. Or define a map or order method in your object to allow comparison in PL/SQL.