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 Go to next message
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 #328123 is a reply to message #328121] Thu, 19 June 2008 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try:
CREATE OR REPLACE TRIGGER nsdlgroup  
BEFORE UPDATE ON UPD_GRP    
FOR EACH ROW
WHEN (NEW.ID <> OLD.ID or NEW.NAME <> OLD.NAME)
BEGIN
  if (:NEW.ID <> :OLD.ID) then
     MY_PROCEDURE(:NEW.ID, :OLD.ID)
  end if;
  if (:NEW.NAME <> :OLD.NAME) then 
     MY_PROCEDURE(:NEW.NAME, :OLD.NAME)
  END IF;
END;

Of course this does not work with NULL.

Quote:
My question is i need call the same procedure for every column updation in same table.

Do you? Only YOU know what your procedure is.

Regards
Michel
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 to
if ( 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Trigger for calling same procedure for different column updation in a tabel [message #328245 is a reply to message #328121] Thu, 19 June 2008 08:38 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above
Previous Topic: comparing data in the same field and table
Next Topic: to drop a user
Goto Forum:
  


Current Time: Mon Dec 05 02:47:33 CST 2016

Total time taken to generate the page: 0.10486 seconds