• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 468
  • Last Modified:

using/invoking trigger

hi guys

In ORacle SQL Developer i see few triggers under the Triggers folder.

The goal of our trigger is to create a a new primary key which is stored in prod_id


create or replace TRIGGER prodschema.prod_ins_id
BEFORE
   INSERT ON prodschema.product
    FOR EACH ROW WHEN (NEW.prod_id IS NULL)
 
        BEGIN
        
          SELECT seq_prod_id.NEXTVAL   -- seq_prod_id is a sequence
                 , 'PROD_CODE' || seq_prod_id.CURRVAL
                 
          INTO  :NEW.prod_id
            , :NEW.prod_code
              
          FROM DUAL;
END;


what i want to know is we have a insert store procedure which inserts rows into the product  table. when this insert procedure is invoked, is the above trigger automatically called ?

I would also like to know what precautions to take when writing and invoking a trigger.

thanks
0
royjayd
Asked:
royjayd
2 Solutions
 
slightwv (䄆 Netminder) Commented:
>>is the above trigger automatically called ?

If the procedure does an insert into that table, then yes the trigger will fire.

>>I would also like to know what precautions to take when writing and invoking a trigger.

There is a large debate on this topic if you search around:
http://www.dba-oracle.com/m_trigger.htm
0
 
sujith80Commented:
The trigger will fire automatically as long as it is ENABLED and VALID

To check if it is ENABLED run the below

select trigger_name, status, ut.*
from all_triggers ut
where owner = 'PRODSCHEMA'
trigger_name = upper('prod_ins_id');

to check the status
select status, ao.*
from all_objects ao
where owner = 'PRODSCHEMA'
and object_name = 'PROD_INS_ID'

>> what precautions to take when writing and invoking a trigger
Few of them
- if there are multiple triggers you need to take care of the sequencing of triggers
- Try to avoid complicated logic
- Try to avoid cross referencing of objects in triggers, this often leads to deadlocks
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now