Home » SQL & PL/SQL » SQL & PL/SQL » Generate triggers on multiple tables (Merged)
Generate triggers on multiple tables (Merged) [message #250001] Fri, 06 July 2007 14:30 Go to next message
abdou_ci
Messages: 5
Registered: May 2007
Location: I
Junior Member
Hi,
I want to create a trigger all table of dba_tables.
But i don't know how to do.
can anyone help me?
Here is my trigger and my select, and i want to combine the two.

Select *
from dba_tables
where owner = 'SIGTASAD'
order by table_name;


CREATE OR REPLACE TRIGGER SIGTASAD.TRG_dba_tables_BEF_INSUPD

BEFORE INSERT OR UPDATE

ON dba_tables

FOR EACH ROW

DECLARE
v_ird_employee_no IRD_EMPLOYEE.ird_employee_no%TYPE;


BEGIN
v_ird_employee_no := PCK_UTIL.get_ird_employee_no( USER );

IF INSERTING THEN
:NEW.enter_user := v_ird_employee_no;
:NEW.enter_date := SYSDATE;
ELSIF UPDATING THEN
:NEW.update_user := v_ird_employee_no;
:NEW.update_date := SYSDATE;
END IF;

END;
/
is it possible?
thanks
Re: script of trigger with and select [message #250002 is a reply to message #250001] Fri, 06 July 2007 14:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You can't create a trigger on DBA_TABLES. Even if it'd be possible you should not try to create a trigger on SYS objects
2/ DBA_TABLES is a view and there is no INSERT or UPDATE trigger on a view
3/ Triggers can only referenced columns that are in the object and enter_user, enter_object... are not part of dba_tables.

If you explain what's your goal maybe we can help you.

Regards
Michel

[Updated on: Fri, 06 July 2007 14:43]

Report message to a moderator

script on trigger [message #250011 is a reply to message #250001] Fri, 06 July 2007 20:08 Go to previous messageGo to next message
abdou_ci
Messages: 5
Registered: May 2007
Location: I
Junior Member
Ok
I 've a new database, i create a script to add collumn on dba_tables. here is a script:
select 'ALTER TABLE ',substr(table_name,1,40),' add(enter_user number(6)
,update_user number(6)
,enter_date date
,update_date date); '
from dba_tables
where owner = 'SIGTASAD'
order by table_name;

then
i have a script to create a trigger on each main table; here is an exemple of creating table on a table:

CREATE OR REPLACE TRIGGER SIGTASAD.TRG_payment_loc_BEF_INSUPD BEFORE INSERT OR UPDATE
ON payment_loc

FOR EACH ROW
DECLARE

v_ird_employee_no IRD_EMPLOYEE.ird_employee_no%TYPE;

BEGIN

v_ird_employee_no := PCK_UTIL.get_ird_employee_no( USER );

IF INSERTING THEN
:NEW.enter_user := v_ird_employee_no;
:NEW.enter_date := SYSDATE;
ELSIF UPDATING THEN
:NEW.update_user := v_ird_employee_no;
:NEW.update_date := SYSDATE;
END IF;

END;
/

Now my question is instead of taking one by one each main table of dba_table, does it possible to create trigger for all tables of dba_table?
thanks
Re: script on trigger [message #250012 is a reply to message #250011] Fri, 06 July 2007 21:25 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>does it possible to create trigger for all tables of dba_table?
NO, but if your OS is *nix, it is trival to script based upon a file containing a list of all tables to get the trigeger.


Why wasn't the table created with all the needed columns the first time?
Re: script on trigger [message #250016 is a reply to message #250011] Fri, 06 July 2007 22:49 Go to previous message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
abdou_ci wrote on Fri, 06 July 2007 18:08

possible to create trigger for all tables of dba_table?




Yes, you can dynamically create a script using sql to create sql, as you did with your alter table statements, or you can use pl/sql and execute immediate, something like:


DECLARE
  v_sql  VARCHAR2 (32767);
BEGIN
  FOR r IN 
    (SELECT table_name 
     FROM   dba_tables
     WHERE  owner = 'SIGTASAD')
  LOOP
    v_sql := 'CREATE OR REPLACE TRIGGER SIGTASAD.';
    v_sql := v_sql || r.table_name || '_BEF_INSUPD '; 
    ... and so on, until you have built the whole statement to create the trigger
    EXECUTE IMMEDIATE v_sql;    
  END LOOP;
END;
/

Previous Topic: What Privilege are Recd
Next Topic: Display first value only for repeated values in a column
Goto Forum:
  


Current Time: Mon Dec 05 10:52:54 CST 2016

Total time taken to generate the page: 0.07225 seconds