Home » SQL & PL/SQL » SQL & PL/SQL » How to pass object as parameter to procedure while calling from trigger
How to pass object as parameter to procedure while calling from trigger [message #212824] Mon, 08 January 2007 08:14 Go to next message
ram_p_j
Messages: 9
Registered: August 2005
Junior Member
Hi All,

I am doing the following to make an insert into tbl_history table for every update in tbl table..

1. creating table tbl

CREATE TABLE tbl (n number, m number)

2. To insert into table tbl

BEGIN
FOR j IN 1..5 LOOP
INSERT INTO tbl VALUES (j, 0);
END LOOP;
END;

3. create a table tbl_history

CREATE TABLE tbl_history ( d DATE, OLDNUM number, NEWNUM number)

4. Creating a TYPE similiar to tbl(same datatype but different column name)

CREATE OR REPLACE TYPE t AS OBJECT (n NUMBER, m NUMBER)


5. Creating a procedure INSERT_AUDIT1 to make insert into orders_AUDIT

CREATE OR REPLACE
PROCEDURE INSERT_AUDIT1(
V_OLDVALUE t,
V_NEWVALUE t
)AS
BEGIN

INSERT INTO tbl_history(d, oldnum, newnum)
VALUES (sysdate, V_OLDVALUE.m, V_NEWVALUE.m);

END;


6. Creating a trigger on TBL. i am calling the procedure INSERT_AUDIT1 which is taking OBJECT_VALUE as paramters.

create or replace TRIGGER TBL_TRG AFTER UPDATE ON TBL
FOR EACH ROW
BEGIN
INSERT_AUDIT1(:OLD.OBJECT_VALUE,:NEW.OBJECT_VALUE);
END Tbl_Trg;

7. I update tbl table

UPDATE tbl SET tbl.m = 1 where tbl.n= 1;

Trigger fails if i execute in the above sequence. Trigger will be fired successfully when i change the sequence of execution like step 4 first and then step 1, 2 , 3 , 5, 6 and 7.

But i want the first scenario to work bcoz in my case table tbl has been already created and cannot be touched.

My main concern is i want to pass OBJECT as parameter to procedure from trigger instead of passing like :OLD.n, :OLD.m, :NEW.n, :NEW.m. If anybody has alternate solution without changing the sequence of execution please help me..

Please help me guys.. its little urgent...

Thanks & Regards,
Ramachandran

Re: How to pass object as parameter to procedure while calling from trigger [message #212832 is a reply to message #212824] Mon, 08 January 2007 08:45 Go to previous messageGo to next message
Giraffe
Messages: 10
Registered: January 2007
Location: Suffolk, UK
Junior Member
Just call the procedure with expressions that evaluate to objects of type t, i.e. call the constructor of t like this:

CREATE OR REPLACE TRIGGER tbl_trg
AFTER UPDATE ON tbl
FOR EACH ROW
BEGIN
  INSERT_AUDIT1 ( t(:OLD.n, :OLD.m), t(:NEW.n, :NEW.m) );
END tbl_trg;
Re: How to pass object as parameter to procedure while calling from trigger [message #212843 is a reply to message #212832] Mon, 08 January 2007 09:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm afraid I don't believe you when you say that your test case works when you execute the steps in a specific order - your trigger is never going to be valid as there is no column OBJECT_VALUE in scope for it that can be referenced as :NEW and :OLD

Here's an example of what happens when you execute the steps in order 4,1,2,3,5,6,7
SQL> CREATE OR REPLACE TYPE t AS OBJECT (n NUMBER, m NUMBER)
  2  /

Type created.

SQL> 
SQL> CREATE TABLE tbl (n number, m number)
  2  /

Table created.

SQL> 
SQL> BEGIN
  2  FOR j IN 1..5 LOOP
  3  INSERT INTO tbl VALUES (j, 0);
  4  END LOOP;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> 
SQL> CREATE TABLE tbl_history ( d DATE, OLDNUM number, NEWNUM number)
  2  /

Table created.

SQL> 
SQL> CREATE OR REPLACE
  2  PROCEDURE INSERT_AUDIT1( 
  3  V_OLDVALUE t,
  4  V_NEWVALUE t
  5  )AS
  6  BEGIN
  7  INSERT INTO tbl_history(d, oldnum, newnum)
  8  VALUES (sysdate, V_OLDVALUE.m, V_NEWVALUE.m);
  9  END;
 10  /

Procedure created.

SQL> 
SQL> create or replace TRIGGER TBL_TRG AFTER UPDATE ON TBL 
  2  FOR EACH ROW 
  3  BEGIN
  4  INSERT_AUDIT1(:OLD.OBJECT_VALUE,:NEW.OBJECT_VALUE);
  5  END Tbl_Trg;
  6  /

Warning: Trigger created with compilation errors.

SQL> 
SQL> UPDATE tbl SET tbl.m = 1 where tbl.n= 1
  2  ;
UPDATE tbl SET tbl.m = 1 where tbl.n= 1
       *
ERROR at line 1:
ORA-04098: trigger 'JOHN_TEST.TBL_TRG' is invalid and failed re-validation
Re: How to pass object as parameter to procedure while calling from trigger [message #212939 is a reply to message #212843] Mon, 08 January 2007 21:08 Go to previous messageGo to next message
ram_p_j
Messages: 9
Registered: August 2005
Junior Member
Hi,

Please correct the step 2 of yours as create table tbl of t. Sorry it was my typo.

But is it possible to get things done without re-creating the table tbl as its already been created.

If u have any other alternative other than this please let me know..

My requirement is i want to pass only 2 parameters to procedure from trigger i.e NEW values and OLD values.

the reason behind this is i want to implement the same for no of tables which has about 50 columns so i may end up passing 100 parameters which is little odd.

Moreover i feel that lines of code in trigger be kept minimum.. please correct me if am wrong.


Thanks & Regards,
Ramachandran

[Updated on: Mon, 08 January 2007 21:15]

Report message to a moderator

Re: How to pass object as parameter to procedure while calling from trigger [message #212940 is a reply to message #212832] Mon, 08 January 2007 21:11 Go to previous messageGo to next message
ram_p_j
Messages: 9
Registered: August 2005
Junior Member
Hi Giraffe,

Thanks for the reply...

My requirement here is to pass only 2 parameters OLD and NEW values as there is a possiblility of more than 50 columns ending up with 100 parameters in procedure for other tables to which i need to implement the same ...

Thanks & Regards,
Ramachandran

[Updated on: Mon, 08 January 2007 21:12]

Report message to a moderator

Re: How to pass object as parameter to procedure while calling from trigger [message #212942 is a reply to message #212824] Mon, 08 January 2007 21:22 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> My requirement here is to pass only 2 parameters OLD and NEW values as there is a possiblility of more than 50 columns
Rhetorical question - In the above scenario 2 of the 50 columns get changed.
How will you know which columns are associated with the :OLD & :NEW values in the audit table?
Re: How to pass object as parameter to procedure while calling from trigger [message #212949 is a reply to message #212942] Mon, 08 January 2007 22:17 Go to previous messageGo to next message
ram_p_j
Messages: 9
Registered: August 2005
Junior Member
Hi anacedent,

Your question is quite reasonable..

Lets taken an example to make u understand the requirement clearly..

There is a table say EMP_MAST where there are 50 columns like DEPT, ROLE, DESIGNATION SALARY and so on..

We have a MAST_AUDIT table where we will be logging the changes made in the EMP_MAST table.. MAST_AUDIT has lot of columns like maker id, maker date author id and author date..

It will have columns like TABLE_NAME, COLUMN_NAME, OLD_VALUE, NEW_VALUE and so on.

Now lets assume that DEPT column has been updated say from ADMIN to HR.
Similiarly DESIGNATION column has been updated say from ASST MGR to MGR.

Now i would like to pass only 2 parameters OLD and NEW values(could be RECORD or OBJECT or any other) to procedure from trigger instead of :OLD.DEPT , :OLD.DESIGNATION, :NEW.DEPT, :NEW.DESIGNATION and so on..

i will be making 2 enteries into MAST_AUDIT with following datas.

Please note that MAST_AUDIT is going to be common for no of tables like EMP_MAST say DEPT_MAST, PRODUCT_MAST and so on

TABLE_NAME | COLUMN_NAME | OLD_VALUE | NEW_VALUE
------------------------------------------------------------
EMP_MAST | DEPT | ADMIN | HR
EMP_MAST | DESIGNATION | ASST MGR | MGR

Coming back to your question..

That is what my issue is? i want one parameter of OLD(all columns) and another parameter of NEW(all columns) to be passed instead of passing OLD and NEW value of every column.

Every old and new value of a particular column will be checked in the PROCEDURE for the difference. If there is a difference it will be inserted into MAST_AUDIT as above.. I fully understand that its not really a correct way. but i dont see any other alternative as of now.

But u may immediately think of asking me like what is the issue in passing all the OLD and NEW as paramters instead of only 2 parameters as OBJECT or RECORD? but i only concern is to reduce the lines of code in Trigger..

If you have any other feasible solution for this please let me know


Thanks & Regards,
Ramachandran

[Updated on: Mon, 08 January 2007 22:38]

Report message to a moderator

Re: How to pass object as parameter to procedure while calling from trigger [message #213026 is a reply to message #212949] Tue, 09 January 2007 03:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm pretty certain that you can't do what you're trying to. I've looked at this in the past, and the best solution I came up with was to write code to automatically generate the triggers and their contents to avoid me having to type everything in by hand.

The only concern I can think of with regards to trigger size is to keep the execution time to a minimum to reduce the overhead on DML statements that affect that table.

If you're trying to audit changes to the table, then you might be better off using the built in [/url=http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96521/audit.htm]Auditing[/url]
Re: How to pass object as parameter to procedure while calling from trigger [message #213048 is a reply to message #212939] Tue, 09 January 2007 04:02 Go to previous messageGo to next message
Giraffe
Messages: 10
Registered: January 2007
Location: Suffolk, UK
Junior Member
ram_p_j wrote on Tue, 09 January 2007 03:08
Hi,

Please correct the step 2 of yours as create table tbl of t. Sorry it was my typo.

But is it possible to get things done without re-creating the table tbl as its already been created.

Hold on a second. tbl has already been created and has a column of type t. Now you create type t.
How has tbl been created before the type of the column in it?
Do you mean you want to ALTER the table?

The difficulty with storing all changes to all columns in one table is that your old and new value columns in the auditing table need to be able to hold any type of data.

Rather than trying to pass all parameters to the auditing procedure at once (whether as many parameters or as two records), have you considered calling the auditing procedure individually for each column?

By which I mean, rather than
AUDIT(:NEW.a, :OLD.a, :NEW.b, :OLD.b, ... );
or
AUDIT(new_object, old_object);
why not
AUDIT(table_name, 'a', :NEW.a, :OLD.a);
AUDIT(table_name, 'b', :NEW.b, :OLD.b);
...
Re: How to pass object as parameter to procedure while calling from trigger [message #213049 is a reply to message #213026] Tue, 09 January 2007 04:09 Go to previous messageGo to next message
ram_p_j
Messages: 9
Registered: August 2005
Junior Member
Hi JROWBottom,

Thanks for the reply.. URL provided by u is very high level..

Please correct me if am wrong. As u know i would like to have auditing at table level to be very precise at column level..

Is it possible to do something with view of particular table?

i tried with view but trigger can be written only for INSTEAD OF UPDATE and not for AFTER UPDATE..When i use INSTEAD OF UPDATE in views trigger does not throw any error but changes dont get inserted into tbl_history. but update get reflected in both table and view.

that stops me from using VIEW. Any similiar stuffs that will allow me to achieve this...

I want to audit table changes for a limited tables may be 1/5th of the total tables avbl in database.

Please correct me if am wrong..

Thanks & Regards,
Ramachandran



[Updated on: Tue, 09 January 2007 04:12]

Report message to a moderator

Re: How to pass object as parameter to procedure while calling from trigger [message #213052 is a reply to message #213048] Tue, 09 January 2007 04:31 Go to previous messageGo to next message
ram_p_j
Messages: 9
Registered: August 2005
Junior Member
Hi Giraffe,

Please note that i have given 2 scenario's in my first posting.

1. Creating tbl after creating type t.
2. Creating tbl before creating type t.

I hope that u agree that point 1 works fine as tbl will have the reference of OBJECT t ...

but what i wanted point 2 to be successful. but i was unsuccessful as tbl has no reference of OBJECT t which is quite obvious .. but i want point 2 to be successful by doing any minor work arounds without re-creating or altering the table tbl. That was main Agenda for posting this.

Hope i have answered ur query and doubt...

Regarding ur suggestion its quite acceptable if there are less number of columns( say around 10 to 20) but if i consider a table having 100 columns i need to have 100 AUDIT calls

AUDIT(table_name, 'a', :NEW.a, :OLD.a);

one for each column where there might me the possibility of performance issue.

Hope ur convinced with my reply..

Please correct me if am wrong..

Thanks & Regards,
Ramachandran

Re: How to pass object as parameter to procedure while calling from trigger [message #213235 is a reply to message #213052] Tue, 09 January 2007 23:12 Go to previous message
ram_p_j
Messages: 9
Registered: August 2005
Junior Member
Hi Guys,

Let me know whether its possible to do something like this. Posting below a small Code block.. i tried all combinations with some errors or results as not expected.

v_tablename1 := 'LOS_MAS_CITY';
for i in 1..8 loop

select cname into V_COLUMNNAME from col where tname ='LOS_MAS_CITY' and colno = i;

v_oldout := ':OLD.'||V_COLUMNNAME;
v_newout := ':NEW.'||V_COLUMNNAME;

v_query := 'SELECT ' ||v_oldout ||' into v_oldvalue from dual';
dbms_output.put_line(v_query);

-- code here for execution like execute immediate v_query




Gives me the dbms out as

SELECT :OLD.LMC_CITYID_C into v_oldvalue from dual

execution fails and gives me a error like "not all variables bound"


but when i just say select :OLD.LMC_CITYNAME into v_oldvalue from dual without executing the v_query as above its works fine and gives the old value of that column..



Thanks & Regards
Ramachandran

[Updated on: Tue, 09 January 2007 23:25]

Report message to a moderator

Previous Topic: My trigger has run amok and I can't DROP it--Help, please
Next Topic: How to solve Insertion Problem while using sequence.?
Goto Forum:
  


Current Time: Fri Dec 09 06:17:41 CST 2016

Total time taken to generate the page: 0.10329 seconds