14 Replies Latest reply: Jun 4, 2013 10:56 PM by marksmithusa RSS

    Schema level Database triggers

    641395
      Hi all,
      I want write a DML trigger for all the tables in a schema. Is there a way to write it in schema level. There are a large number i.e. 4000 tables are there in the schema and it will be very tiring to wirte same trigger for 4000 times. I am using Oracle 10g. A helpful suggestion will be highly appreciated.

      Thanks and regards

      Sukanta
        • 1. Re: Schema level Database triggers
          Kamran Agayev A.
          Which type of trigger are you going to create? And what will the purpose of this trigger be?
          You can create DDL triggers on schema level

          You can write triggers that fire whenever one of the following operations occurs:

          DML statements (INSERT, UPDATE, DELETE) on a particular table or view, issued by any user

          DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database

          Database events, such as logon/logoff, errors, or startup/shutdown, also issued either by a particular schema/user or by any schema/user in the database

          http://www.sc.ehu.es/siwebso/KZCC/Oracle_10g_Documentacion/server.101/b10743/triggers.htm

          - - - - - - - - - - - - - - - - - - - - -
          Kamran Agayev A. (10g OCP)
          http://kamranagayev.wordpress.com
          • 2. Re: Schema level Database triggers
            Anand...
            I want write a DML trigger for all the tables in a schema. Is there a way to write it in schema level.
            What type of trigger???


            Anand
            • 3. Re: Schema level Database triggers
              641395
              Thanks Kamran,

              I want to create an AFTER INSERT OR UPDATE OR DELETE trigger for all the tables in the schema which will fire after each DML operation on the table.
              • 4. Re: Schema level Database triggers
                Kamran Agayev A.
                It's not possible to create DML trigger on schema level. You can create DDL triggers on schema level
                Can you please explain your purpose, may be we can suggest you another option

                - - - - - - - - - - - - - - - - - - - - -
                Kamran Agayev A. (10g OCP)
                http://kamranagayev.wordpress.com
                • 5. Re: Schema level Database triggers
                  Anand...
                  I want to create an AFTER INSERT OR UPDATE OR DELETE trigger for all the tables in the schema which will fire after each DML operation on the table.
                  From metalink
                  >
                  It's not possible to CREATE a generic SCHEMA or DATABASE TIGGER with DML event,
                  only DDL events are supported.
                  DML events are on Object Level.
                  >


                  HTH
                  Anand
                  • 6. Re: Schema level Database triggers
                    Pavan DBA
                    its better to enable auditing at schema level instead of triggers.
                    • 7. Re: Schema level Database triggers
                      641395
                      Hi all,
                      Thanks a lot for your suggestions.
                      Is there any way to write a trigger and then apply it for all the tables in the schema. My purpose is to insert some information in a "MY_TABLE" table whenever a DML operation occurs in any of the tables in the schema. Audit option is not a viable solution for this purpose.

                      Sukanto
                      • 8. Re: Schema level Database triggers
                        Kamran Agayev A.
                        In this situation the only way to solve this problem, is to generate creation code of triggers automatically
                        Look at this example
                        SQL> create user test_trigger identified by kamran;
                        
                        User created.
                        
                        SQL> grant dba to test_trigger;
                        
                        Grant succeeded.
                        
                        SQL> conn test_trigger/kamran
                        Connected.
                        SQL> create table tab1 (id number);
                        
                        Table created.
                        
                        SQL> create table tab2 (id number);
                        
                        Table created.
                        
                        SQL> create table tab1_2_log (information varchar2(20));
                        
                        Table created.
                        
                        SQL> DECLARE
                          2     CURSOR all_tables
                          3     IS
                          4        SELECT table_name
                          5          FROM user_tables
                          6         WHERE table_name <> 'TAB1_2_LOG';
                          7
                          8     v_id   NUMBER := 1;
                          9  BEGIN
                         10     FOR rec_cur IN all_tables
                         11     LOOP
                         12        EXECUTE IMMEDIATE    'create or replace trigger trg_'
                         13                          || rec_cur.table_name
                         14                          || '
                         15  before insert or update or delete on '
                         16                          || rec_cur.table_name
                         17                          || '
                         18  declare
                         19  begin
                         20  if UPDATING then
                         21  insert into tab1_2_log values(''UPDATING on '||rec_cur.table_name||''');
                         22  elsif DELETING then
                         23  insert into tab1_2_log values(''DELETING on '||rec_cur.table_name||''');
                         24  elsIF INSERTING then
                         25  insert into tab1_2_log values(''INSERTING on '||rec_cur.table_name||''');
                         26  end if;
                         27  end;';
                         28     END LOOP;
                         29  END;
                         30  /
                        
                        PL/SQL procedure successfully completed.
                        
                        SQL> insert into tab1 values(1);
                        
                        1 row created.
                        
                        SQL> update tab1 set id=1;
                        
                        1 row updated.
                        
                        SQL> delete from tab1;
                        
                        1 row deleted.
                        
                        SQL> insert into tab2 values(1);
                        
                        1 row created.
                        
                        SQL> update tab2 set id=1;
                        
                        1 row updated.
                        
                        SQL> delete from tab2 ;
                        
                        1 row deleted.
                        
                        SQL> select * from tab1_2_log;
                        
                        INFORMATION
                        --------------------
                        INSERTING on TAB1
                        UPDATING on TAB1
                        DELETING on TAB1
                        INSERTING on TAB2
                        UPDATING on TAB2
                        DELETING on TAB2
                        
                        6 rows selected.
                        
                        SQL>
                        - - - - - - - - - - - - - - - - - - - - -
                        Kamran Agayev A. (10g OCP)
                        http://kamranagayev.wordpress.com
                        • 9. Re: Schema level Database triggers
                          Anand...
                          Thanks a lot for your suggestions.
                          Is there any way to write a trigger and then apply it for all the tables in the schema. My purpose is to insert some information in a "MY_TABLE" table whenever a DML operation occurs in any of the tables in the schema.
                          >

                          Kamran, has given a solution, for your problem.But what will be impact on the database when huge amount of row will be inserted or updated or deleted on the many tables in the schema .Its good in case where you need to do dml for very few rows of a table.
                          Audit option is not a viable solution for this purpose.
                          Who said so. I still think object level AUDIT is the best option to go for.

                          Anand
                          • 10. Re: Schema level Database triggers
                            Kamran Agayev A.
                            Anand is right. You should use ADUIT rather than creating so many triggers for each table of your database
                            If you don't want to use AUDIT, then refer to my solution
                            Good luck

                            - - - - - - - - - - - - - - - - - - - - -
                            Kamran Agayev A. (10g OCP)
                            http://kamranagayev.wordpress.com
                            • 11. Re: Schema level Database triggers
                              926949
                              hello,

                              i m also facing same problem.
                              please help me out if u got any answer.
                              is it possible to creating dml trigger on schema level?
                              • 12. Re: Schema level Database triggers
                                900211
                                Hi Kamran - you mentioned that we can use auditing. It may be fine if all we want to do is to track data changes.

                                But what if we want to implement additional processing logic. example: set value of one column based upon value entered in another.
                                • 13. Re: Schema level Database triggers
                                  marksmithusa
                                  sukanto wrote:
                                  Hi all,
                                  I want write a DML trigger for all the tables in a schema. Is there a way to write it in schema level. There are a large number i.e. 4000 tables are there in the schema and it will be very tiring to wirte same trigger for 4000 times. I am using Oracle 10g. A helpful suggestion will be highly appreciated.
                                  Helpful suggestion: don't do it.

                                  This is where I would usually include my random monologue about how evil triggers are, you can never trust them, they'll stab you in the back the first chance they get, etc.

                                  But I think this is WAY beyond anything remotely attached to sanity that I'm not even going to bother. I'd actually be genuinely interested to hear how much of a complete cluster this ends up being.

                                  4,000 triggers! I'd presume that was some kind of elaborate prank if that request ever came across my desk. Failing that, I'd make you type out the DDL for each trigger individually for multiple (at least four) test environments before rejected it when it came to QA.
                                  • 14. Re: Schema level Database triggers
                                    marksmithusa
                                    923946 wrote:
                                    hello,

                                    i m also facing same problem.
                                    please help me out if u got any answer.
                                    is it possible to creating dml trigger on schema level?
                                    Oh, wow. There's a SECOND person who wants to do this?

                                    You'd think the odds of TWO people wanting to try this out (hopefully before they do it in Prod) would be greater than 6 billion to one, but apparently not.