Home » SQL & PL/SQL » SQL & PL/SQL » Trigger for calling same procedure for different column updation in a tabel (Oracle 10g, XP)
Trigger for calling same procedure for different column updation in a tabel [message #328121] |
Thu, 19 June 2008 00:12  |
suneelvarma_27
Messages: 28 Registered: April 2008
|
Junior Member |
|
|
Hi,
when i am tring to update ID column of UPD_GRP table, this trigger fired successfully...
CREATE OR REPLACE TRIGGER nsdlgroup
BEFORE UPDATE ON UPD_GRP
FOR EACH ROW
WHEN (NEW.ID <> OLD.ID)
CALL MY_PROCEDURE(:NEW.ID, :OLD.ID)
but when i am tring to update ID , NAME columns of UPD_GRP table
the following trigger is not fired
CREATE OR REPLACE TRIGGER nsdlgroup
BEFORE UPDATE ON UPD_GRP
FOR EACH ROW
BEGIN
IF UPDATING('ID') THEN
if (:NEW.ID <> :OLD.ID) then
CALL MY_PROCEDURE(:NEW.ID, :OLD.ID)
end if;
END IF;
IF UPDATING('NAME') THEN
if (:NEW.NAME <> :OLD.NAME) then
CALL MY_PROCEDURE(:NEW.NAME, :OLD.NAME)
end if;
END IF;
END;
My question is i need call the same procedure for every column updation in same table.
Thanks in advance...
[Updated on: Thu, 19 June 2008 00:19] Report message to a moderator
|
|
|
|
Re: Trigger for calling same procedure for different column updation in a tabel [message #328127 is a reply to message #328121] |
Thu, 19 June 2008 00:58   |
suneelvarma_27
Messages: 28 Registered: April 2008
|
Junior Member |
|
|
my procedure is
create or replace procedure MY_PROCEDUTE(x in varchar2,y varchar2)
is
begin
if (substr(x,1,1) <> substr(y,1,1)) then
insert into audit_table values (substr(y,1,1),substr(x,1,1));
end if;
if ( substr(x,2,1) <> substr(y,2,1)) then
insert into audit_tablevalues (substr(y,2,1),substr(x,2,1),);
end if;
if ( substr(x,3,1) <> substr(y,3,1)) then
insert into audit_table values (substr(y,3,1),substr(x,3,1);
end if;
if ( substr(x,4,1) <> substr(y,4,1)) then
insert into audit_table values (substr(y,4,1),substr(x,4,1);
end if;
if ( substr(x,5,1) <> substr(y,5,1)) then
insert into audit_table values (substr(y,5,1),substr(x,5,1);
end if;
if ( substr(x,6,1) <> substr(y,6,1)) then
insert into audit_table values (substr(y,6,1),substr(x,6,1);
end if;
if ( substr(x,7,1) <> substr(y,7,1)) then
insert into audit_table values (substr(y,7,1),substr(x,7,1);
end if;
if ( substr(x,8,1) <> substr(y,8,1)) then
insert into audit_table values (substr(y,8,1),substr(x,8,1);
end if;
end;
this procedure takes two strings and compare that strings..
if two positions are not equal then it will insert old and new values into the audit_table
|
|
|
Re: Trigger for calling same procedure for different column updation in a tabel [message #328130 is a reply to message #328123] |
Thu, 19 June 2008 01:05   |
suneelvarma_27
Messages: 28 Registered: April 2008
|
Junior Member |
|
|
Hi Michel Cadot,
i am trying to execute the code posted by u, it is giving same error as previous....
anyways thanks for the quick replay.
Error report:
SQL Error: ORA-04098: trigger 'SYSTEM.NSDLGROUP' is invalid and failed re-validation
04098. 00000 - "trigger '%s.%s' is invalid and failed re-validation"
*Cause: A trigger was attempted to be retrieved for execution and was found to be invalid. This also means that compilation/authorization failed for the trigger.
*Action: Options are to resolve the compilation/authorization errors, disable the trigger, or drop the trigger.
[Updated on: Thu, 19 June 2008 12:54] by Moderator Report message to a moderator
|
|
|
Re: Trigger for calling same procedure for different column updation in a tabel [message #328131 is a reply to message #328127] |
Thu, 19 June 2008 01:07   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Where is the use in this? You don't even store the position of the character, nor any indication about which record it refers toif ( substr(x,2,1) <> substr(y,2,1)) then
insert into audit_tablevalues (substr(y,2,1),substr(x,2,1),);
end if;
This is definitely NOT your real code.
|
|
|
Re: Trigger for calling same procedure for different column updation in a tabel [message #328132 is a reply to message #328121] |
Thu, 19 June 2008 01:08   |
tejas_gandhi
Messages: 8 Registered: August 2007
|
Junior Member |
|
|
Quote: |
but when i am tring to update ID , NAME columns of UPD_GRP table
the following trigger is not fired
|
From oracle manual i got following:
The INSERTING, DELETING, and UPDATING conditional
predicates cannot be used for the CALL procedures; they can only
be used in a PL/SQL block.
And why you want procedure to check new and old values? your procedure will insert characters not full string.
|
|
|
Re: Trigger for calling same procedure for different column updation in a tabel [message #328134 is a reply to message #328131] |
Thu, 19 June 2008 01:25   |
suneelvarma_27
Messages: 28 Registered: April 2008
|
Junior Member |
|
|
Hi Frank,
your right, that is not my real code..
my origianl code is
if ( substr(x,2,1) <> substr(y,2,1)) then
insert into audit_table values (100,'insert','dmatfunction',substr(y,2,1),substr(x,2,1));
end if;
Ex: 1001 old 0001 new
1st position is update operation, 2 nd is insert operation, 3rd is select operation is select operation 4th is upload operation)
i need to insert this changes into audit_table if the corresponding substring changes..
my UPD_GRP table is
grpid DMAT_operations RMAT_operations
100 1000 1000
if i want to change DMAT_operations, RMAT_operations to 1001 and 0000 then the audit_table looks like
grpid operation function old_value new_value
100 upload dmat_function 0 1
100 update rmat_function 1 0
|
|
|
Re: Trigger for calling same procedure for different column updation in a tabel [message #328136 is a reply to message #328132] |
Thu, 19 June 2008 01:33   |
suneelvarma_27
Messages: 28 Registered: April 2008
|
Junior Member |
|
|
Hi ejas_gandhi,
i have combined the operations into string 1001
in this string
1 insert
0 update
0 select
1 upload
if any changes to this string, i need to insert appropriate change record into the audit_table. for that i have divided the string into sub string and comparing. if two substrings are equal then no record will be inserted. else i need to insert corresponding record. like
if i updated the above string to 1000 then audit_table should be like this
grpid function operation old new
100 dmat upload 1 0
becas i have changed only 4th string that is upload operation.
|
|
|
|
Goto Forum:
Current Time: Sat Feb 15 08:44:51 CST 2025
|