1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

If a trigger is in a separate file then will it fire if script is executed in another

Discussion in 'SQL PL/SQL' started by pditty8811, Feb 25, 2014.

  1. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    If a trigger is in its own separate file then will it fire if script is executed in another file to trigger it, without starting the trigger script using "START" ?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: If a trigger is in a separate file then will it fire if script is executed in ano

    What is the question? Obviously you're confused and you are confusing us.
     
  3. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Re: If a trigger is in a separate file then will it fire if script is executed in ano

    Stop trolling!
     
  4. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Re: If a trigger is in a separate file then will it fire if script is executed in ano

    How do I fire a trigger? Do I compile it in a seperate script file, then execute the Update in another? Or can I do both in the same script file.
     
  5. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Re: If a trigger is in a separate file then will it fire if script is executed in ano

    How does Oracle know that I have an insert trigger that will fire after I do an insert, if the insert trigger isn't compiled?
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Re: If a trigger is in a separate file then will it fire if script is executed in ano

    NO,

    If your trigger is not compiled, Oracle doesn't fire your trigger when doing insert.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: If a trigger is in a separate file then will it fire if script is executed in ano

    I am NOT 'trolling.'


    Possibly if you could post the question so others could understand it ...
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: If a trigger is in a separate file then will it fire if script is executed in ano

    Because it's one of the dependent objects associated with that table. As long as it's valid it will fire when the triggering event occurs.
     
  9. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Re: If a trigger is in a separate file then will it fire if script is executed in ano

    That's two separate issues. The Oracle database is called relational for a reason. Plz forgive me if that sounds snarky but it's important that you understand that.

    If you look at the trigger syntax you will see the trigger must be associated w/ a table. It's entirely up to you what the trigger should do but it MUST do something else why have it? Within the database there are default tables/views where you can find all the details about your trigger. What it's called, who created it, when, what table its associated with, who owns the table. etc..., etc..., etc... You can also see if the trigger is currently valid (I think that's the STATUS column). If the trigger is valid then, by definition, the trigger will fire based upon the event you defined for the table you identified. If it's not valid then the DB will simply ignore it.

    Methinks you are making this harder than it needs to be (overthinking it) - OR - you want the trigger to do too much. Triggers should be targeted to do a single, individual thing...just like a function...do ONE thing. Period.


    HTH

    CJ
     
    pditty8811 likes this.
  10. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Re: If a trigger is in a separate file then will it fire if script is executed in ano

    Thanks.

    How do I put this in a trigger then?

    Code (Text):

      --Find value of Detail
      SELECT (ORDERITEMS.DETAIL + 1)
      INTO vDetail
      FROM ORDERITMES
      WHERE ORDERITEMS.ORDERID = vOrderid;
     
      --Assign detail value of 1 if previous detail value is null
      IF vDetail IS NULL THEN
          vDetail := 1;
        END IF;
     
    How do I declare variable vDetail inside a trigger?
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: If a trigger is in a separate file then will it fire if script is executed in ano

    The same way you'd declare any variable.


    Code (SQL):

    CREATE OR REPLACE TRIGGER yabbadabbado
    BEFORE INSERT ON bubba
    FOR each ROW
    DECLARE
         vDetail NUMBER:=NULL;
    ...
    BEGIN
    ...
     --Find value of Detail
      SELECT (ORDERITEMS.DETAIL + 1)
      INTO vDetail
      FROM ORDERITMES
      WHERE ORDERITEMS.ORDERID = vOrderid;
     
      --Assign detail value of 1 if previous detail value is null
      IF vDetail IS NULL THEN
          vDetail := 1;
        END IF
    ...
     
     
    pditty8811 likes this.
  12. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Re: If a trigger is in a separate file then will it fire if script is executed in ano

    Thanks. I didn't realize you could do that in a trigger.