Forum Stats

  • 3,852,705 Users
  • 2,264,128 Discussions
  • 7,905,121 Comments

Discussions

Categories

Basic Question on Oracle Trigger Execution

715303
715303 Member Posts: 87
edited Jan 13, 2010 10:55AM in SQL & PL/SQL
Say for example that I have this trigger code:
CREATE TRIGGER schema.trigger_name 
    BEFORE 
    DELETE OR INSERT OR UPDATE 
    ON schema.table_name 
       pl/sql_block
END trigger_name;
ALTER TRIGGER schema.trigger_name ENABLE;
commit;
The weird part is I have written this trigger but do not know how to execute it. Can someone give me answer for these question, please?

1. How do I add this trigger on a table "table_1"
2. I want the trigger to be permanent on the table_1 and not only for a particular session. How do I do that?
3. Is there anything else I need to take care of while executing a trigger? Something like Best Practices?

I am on Linux. So, please give your solution based on SQL Plus

EDIT: Also How can I ensure that the trigger is there. Is there any way to see it?

Edited by: TuX4EvA on Jan 6, 2010 3:24 AM
Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Pierre Forstmann
    Pierre Forstmann Member Posts: 6,961 Silver Crown
    edited Jan 6, 2010 6:32AM Answer ✓
    1.
    Replace
    ON schema.table_name
    by 
    on schema.table1
    2. Table triggers are always permanent and not for a given session.

    To check that trigger is created in database dictionary, you can use
     select owner, trigger_name from all_triggers;
    To learn more about triggers, please have a look to Oracle® Database 2 Day Developer's Guide:
    http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10766/tdddg_triggers.htm#BABIHIHH

    Edited by: P. Forstmann on 6 janv. 2010 12:28

    Edited by: P. Forstmann on 6 janv. 2010 12:31
    · Share on TwitterShare on Facebook

Answers

  • Pierre Forstmann
    Pierre Forstmann Member Posts: 6,961 Silver Crown
    edited Jan 6, 2010 6:32AM Answer ✓
    1.
    Replace
    ON schema.table_name
    by 
    on schema.table1
    2. Table triggers are always permanent and not for a given session.

    To check that trigger is created in database dictionary, you can use
     select owner, trigger_name from all_triggers;
    To learn more about triggers, please have a look to Oracle® Database 2 Day Developer's Guide:
    http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10766/tdddg_triggers.htm#BABIHIHH

    Edited by: P. Forstmann on 6 janv. 2010 12:28

    Edited by: P. Forstmann on 6 janv. 2010 12:31
    · Share on TwitterShare on Facebook
  • Twinkle
    Twinkle Member Posts: 740 Silver Badge
    Hi,

    Just replace
    ON schema.table_name 
with
ON schema.table_1
    Do you want the trigger as row level trigger, i.e It will check for each row in table_1 then you will need to add
    For Each Row
    else It will be a statement level trigger i.e it will be called before the DML

    source: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm

    To ensure that the trigger is there?

    select * from user_triggers where table_name ='TABLE_1';
    Twinkle
    Twinkle
    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,490 Red Diamond
    Hi,
    TuX4EvA wrote:
    Say for example that I have this trigger code:
    CREATE TRIGGER schema.trigger_name 
BEFORE 
DELETE OR INSERT OR UPDATE 
ON schema.table_name 
pl/sql_block
END trigger_name;
ALTER TRIGGER schema.trigger_name ENABLE;
commit;
    The weird part is I have written this trigger but do not know how to execute it. Can someone give me answer for these question, please?
    The trigger will "fire" (that is, be executed) when you issue a DELETE, INSERT or UPDATE statement on table_name. It's auotmatic. You don't have to explicitly ask for it to run, and, short of disabling the trigger, there's nothing you can to to keep it from firing.
    >
    1. How do I add this trigger on a table "table_1"
    Make a copy of hte trigger, with a unique name. Change "table_name" to "table_1".
    2. I want the trigger to be permanent on the table_1 and not only for a particular session. How do I do that?
    Triggers are always permanent, and apply to all sessions. You don't have to do anything special.
    3. Is there anything else I need to take care of while executing a trigger? Something like Best Practices?
    Two common mistakes in triggers are:
    (1) trying to COMMIT. Don't.
    (2) Using EXCEPTION to hide all errors. EXCEPTIONs should be used to ignore only very specific errors, for example, NO_DATA_FOUND for a particular SELECT statement. When you use "WHEN OTHERS" it should be for lthings like logging errors, and the EXCEPTION block should RAISE the error again after it is done.
    EDIT: Also How can I ensure that the trigger is there. Is there any way to see it?
    Use the data dictionary.
    USER_TRIGGERS (and ALL_TRIGGERS) has one row per trigger.
    USER_SOURCE (and ALL_SOURCE) has the full code.
    Frank Kulash
    · Share on TwitterShare on Facebook
  • 742140
    742140 Member Posts: 1
    PLZ SEND ME SCHEMA OF TRIGGER & PACKAGE
    · Share on TwitterShare on Facebook
  • marcusafs
    marcusafs Member Posts: 294 Red Ribbon
    Concerning best practices.

    In all my triggers I include an exception handler that calls a procedure to log the error to a database table and send me an email. DBMS_OUTPUT will not work in a trigger because triggers do not output to the screen. Be sure to include the trigger name and data that will help you identify and fix the trigger.
    · Share on TwitterShare on Facebook
This discussion has been closed.