rupa, September 13, 2001 - 2:18 pm UTC
Oh, darn.
Catherine Devlin, September 10, 2003 - 12:36 pm UTC
I'd been wondering if there was any way to do this. I've been advised to remove the guts of my triggers and put them into procedures, then call the procedures from the triggers, so that I can use unit testing (utPLSQL!) on the procedures. Of course, that involves a LOT of tedious and bulky parameter passing that would be so much easier if we could do this. A script to write the tedious assignments is better than nothing, but it still makes for bulky code.
I'm glad to know that the answer, even if I'm disappointed that it's "no". I guess I should go file an enhancement request somewhere if I'm so upset...
Temporary table
A reader, September 07, 2004 - 8:00 pm UTC
I have a table "mytable" with 200+ columns. I have a 10-page query against this table. Now the requirement is to record the output from this query in a row-level trigger on the table.
Yes, I can take the query and prefix all the columns with a :new. and INSERT INTO t select :new... from dual, but this is really cumbersome because the query is 10-page long and I am too lazy to change it!
Instead, what if I create a GTT like
create global temporary table gtt as select * from mytable where 1=2;
<missing part to populate the GTT from "current" record in trigger>
insert into t select * from gtt;
Can you help me fill in the missing part above? Basically, I just need to do a global search and replace in my 10-page query of column_name with :new.column_name!
Thanks
September 08, 2004 - 8:22 am UTC
<quote>
Now the requirement is to record the output from this query in a
row-level trigger on the table.
</quote>
huh?
this is not making any sense to me.
Temporary table
A reader, September 07, 2004 - 8:01 pm UTC
Forgot to mention, I am on 9iR2
Temporary table
A reader, September 07, 2004 - 10:24 pm UTC
Hm, once I put it that way, I realized that a simple Unix-style solution would work for me
spool sed
select 's/'||column_name||'/:new.'||column_name||'/g'
from user_tab_columns
where table_name='MYTABLE';
spool off
sed -f sed.lst myquery.sql > trigger.sql
Edit trigger.sql as needed
Thanks
September 08, 2004 - 9:04 am UTC
or
select ':new.' || column_name from user_tab_columns where table_name = 'MYTABLE'
??
Temporary table
A reader, September 08, 2004 - 10:25 am UTC
What I meant was, I have a huge query
select <200+ columns involving decodes, case, scalar subqueries, etc
from mytable
where pk=:bv
I need to put a row-level trigger on 'mytable' to capture the output of this query for the current row i.e. replace :bv above with ":new.pk" and replace "from mytable" with "from dual" and replace all the column names with "new.column_name"
Thats why I came up with the Unix "sed" solution to just replace all the column names with ":new.column_name"
Thanks
September 08, 2004 - 10:37 am UTC
there is no such thing as a "row level trigger on a query"
therein lies my confusion.
Temporary table
A reader, September 08, 2004 - 10:57 am UTC
Wrong choice of words on my part, I explained as much as I could above.
Basically, I have a query against a single table that is working fine. Coincidentally, there has emerged a need to run this same query whenever any row is modified in the table and save the output to another table. Of course, in a row-level trigger I cannot select from the table (table is mutating) so I just need the query to run for the current pk (:new.pk) ...
Thanks
Get table name inside trigger
Teymur Hajiyev, September 09, 2004 - 2:22 am UTC
Dear Tom.
I use table name inside trigger code, which current trigger bases on.
Now, is there any option to take its' base table name?
I can take it as select table_name into var_tab_name from user_triggers where trigger_name = 'Current_Trigger_Name';
Is there way as we use %TYPE?
Thanks in advance.
September 09, 2004 - 8:21 am UTC
confusion -- why wouldn't you just put the tablename into the trigger as you are writing it? I mean - a trigger is for *a table*, just code it in there?
This is universal
Teymur Hajiyev, September 10, 2004 - 1:20 am UTC
Thanks Tom.
I agree, but when I am going to change table name, I do not need to change trigger code again, may be recompiling trigger again.
Any idea?
Thanks in advance.
September 10, 2004 - 8:31 am UTC
How often do you really rename your tables?
Really -- "universally" -- how often do you do this and isn't like "a whole bunch of code affected" here.
You better be updating your source code -- you'll want to change the "create or replace trigger X on TABLE_NAME <<<<=====" in your source code control system -- so, therefore you are already there.
(if you don't maintain your code in source code control, you cannot install your application, you are already touching the triggers and all DDL performed on this newly renamed table. No one would just "rename" a table and not update all of their source code -- one year from now, nothing you have would actually "install")
dynamic triger on a cloumn -- oracle 9205
Baqir Hussain, March 22, 2005 - 6:07 pm UTC
I want to be able to specify X ( X means columns) dynamically. Actually, I want to be able to loop over a list of columns in the zip_transaction table and
execute the IF block for each one of them:
CREATE OR REPLACE TRIGGER trans_history_new_trigger
BEFORE UPDATE ON zip_transaction
FOR EACH ROW
BEGIN
IF(:old.X != :new.X OR
:old.X IS NULL AND :new.X IS NOT NULL OR
:old.X IS NOT NULL AND :new.X IS NULL) THEN
INSERT INTO transaction_history_new
(transaction_id, change_column, old_value, new_value, change_date)
VALUES(:new.transaction_id, 'X', :old.X, :new.X, SYSDATE);
END IF;
END;
/
I would very much appreciate if you please help me in this regard. Thanks
March 23, 2005 - 1:04 am UTC
see above to see why you cannot and then search for
audit generic
for an approach you can take instead.
Dynamic Triggers
Kamal, May 31, 2005 - 10:35 am UTC
Hi Tom,
I am creating a trigger using dynamic SQL, when i am executing the SQL using Execute Immediate the trigger is getting created. But when i check it out in the ALL_OBJECTS it is INVALID. How to make it valid..I tried to Recompile it but it is still INVALID.But if i take the text from the ALL_TRIGGERS and create the trigger it is working fine..it becomes VALID.
Declare
lc_trigger_create varchar2(4000);
Begin
lc_trigger_create := 'create or replace trigger trg_name After INSERT Or UPDATE Or DELETE on emp BEGIN End;';
EXECUTE IMMEDIATE lc_trigger_create;
End;
select * from all_objects where object_name like 'TRG_NAME';
What may be reason for this and how to create trigger through dynamic sql. Thanks
May 31, 2005 - 6:28 pm UTC
you need to create a trigger with valid syntax in order for it to ever be 'valid'
'..... begin NULL; end;'
the trigger needs at least an "empty" statement.
Dynamic Triggers
Kamal, June 01, 2005 - 3:11 am UTC
Hi Tom
Thanks for your reply..Actually i just gave the previous statement as example...later i found that the Trigger is INVALID only when there is a DECLARE part in the body of the trigger. For example
Declare
lc_trigger_create varchar2(4000);
p_table_name varchar2(100);
p_pk_column varchar2(100);
Begin
p_table_name := 'EMP';
p_pk_column := 'EMPNO';
lc_trigger_create := 'CREATE OR REPLACE TRIGGER ' || p_table_name || '_IDU_P2_TRG' || CHR(13) || ' After INSERT Or UPDATE Or DELETE ON '|| p_table_name ;
lc_trigger_create := lc_trigger_create || CHR(13) ||' For Each ROW ';
lc_trigger_create := lc_trigger_create ||CHR(13) || ' DECLARE ';
lc_trigger_create := lc_trigger_create || CHR(13) ||' lc_table_name VARCHAR2(50); ';
lc_trigger_create := lc_trigger_create || CHR(13) || ' Begin NULL; End;';
EXECUTE IMMEDIATE lc_trigger_create;
End;
The Trigger Object is Invalid after execution of this script. But if run the same create trigger statement as follows
Create or replace Trigger EMP_IDU_P2_TRG After Insert or Delete or Update on EMP For Each Row
Declare l_table_name varchar2(50);
Begin Null; End;
It is becoming a Valid Object.
Thanks
kamal
June 01, 2005 - 9:04 am UTC
so, look at the trigger text and find the bug in the trigger you are creating.
chr(13) is a carriage return.
chr(10) is an "end of line"
chr(13)||chr(10) is a windows carriage return/line feed end of line.
change 13 to 10, or use my preferred method, just type:
ops$tkyte@ORA9IR2> declare
2 lc_trigger_create varchar2(4000);
3 p_table_name varchar2(100);
4 p_pk_column varchar2(100);
5 Begin
6 p_table_name := 'EMP';
7 p_pk_column := 'EMPNO';
8
9 lc_trigger_create := '
10 CREATE OR REPLACE TRIGGER ' || p_table_name || '_IDU_P2_TRG
11 After INSERT Or UPDATE Or DELETE ON '|| p_table_name || '
12 For Each ROW
13 DECLARE
14 lc_table_name VARCHAR2(50);
15 begin
16 null;
17 end;';
18
19 EXECUTE IMMEDIATE lc_trigger_create;
20
21 End;
22 /
PL/SQL procedure successfully completed.
You don't need to try and force end of lines in there, just hit enter.
My solution for this quite popular problem
Rustam Kafarov, October 14, 2014 - 9:55 am UTC
I've decided to answer on this question as I'd heard it many times and never seen any solution.
So the idea is to have one generic "structure independent" trigger calling some PL/SQL procedure passing entire new and old row.
I used object table for that.
create or replace type test_table_type as object(
... your attributes ....
);
create table test of test_table_type;
create or replace trigger trg_test_upd
before update on test
for each row
l_new_row test_table_type;
l_old_row test_table_type;
l_row_id raw(40000);
begin
l_new_row := :new.sys_nc_rowinfo$; -- <-- this is your new row
l_old_row := :old.sys_nc_rowinfo$; -- <-- this is your old row
l_row_id := :old.sys_nc_oid$; -- <-- this your row logical ID (not rowid!!!). You can use it if you don't have primary key or it is composite.
-- here you can do what ever you want with you old/new rows.
...
end;
/
I like this method as it allows to define common attributes in base type and inherit other types from this type. So you can use generic code even for many tables based on same base type.
its showing error
A reader, June 20, 2019 - 8:15 am UTC
hello sir, the program you have written is showing an error as create statement is wrong
June 20, 2019 - 3:31 pm UTC
What exactly is erroring?