Forum Stats

  • 3,827,727 Users
  • 2,260,812 Discussions
  • 7,897,362 Comments

Discussions

AFTER INSERT TRIGGER with Condition

user8987217
user8987217 Member Posts: 3
edited Jun 5, 2015 12:05PM in SQL & PL/SQL

I am new to PL/SQL programming and i've been task to create a simple trigger; see code below:

<span class="kwd" style="color: #00008b;">CREATE</span><span class="pln" style="color: #000000;"> </span><span class="kwd" style="color: #00008b;">OR</span><span class="pln" style="color: #000000;"> REPLACE </span><span class="kwd" style="color: #00008b;">TRIGGER</span><span class="pln" style="color: #000000;">  TRG_ACCT_IDW </span>
<span class="pln" style="color: #000000;">AFTER </span><span class="kwd" style="color: #00008b;">INSERT</span><span class="pln" style="color: #000000;"> </span><span class="kwd" style="color: #00008b;">ON</span><span class="pln" style="color: #000000;"> ACNTGROUPS </span>
<span class="pln" style="color: #000000;"></span><span class="kwd" style="color: #00008b;">FOR</span><span class="pln" style="color: #000000;"> EACH </span><span class="kwd" style="color: #00008b;">ROW</span><span class="pln" style="color: #000000;"> </span>
<span class="pln" style="color: #000000;"></span><span class="kwd" style="color: #00008b;">BEGIN</span><span class="pln" style="color: #000000;"> </span><span class="kwd" style="color: #00008b;">INSERT</span><span class="pln" style="color: #000000;"> </span>
<span class="pln" style="color: #000000;"></span><span class="kwd" style="color: #00008b;">INTO</span><span class="pln" style="color: #000000;"> IDWORKS_HC </span><span class="pun" style="color: #000000;">(</span><span class="pln" style="color: #000000;">ACCOUNT</span><span class="pun" style="color: #000000;">,</span><span class="pln" style="color: #000000;">IDW_CATEGORYNO</span><span class="pun" style="color: #000000;">,</span><span class="pln" style="color: #000000;">GRTYPE</span><span class="pun" style="color: #000000;">)</span><span class="pln" style="color: #000000;"> </span>
<span class="pln" style="color: #000000;"></span><span class="kwd" style="color: #00008b;">VALUES</span><span class="pln" style="color: #000000;"> </span>
<span class="pln" style="color: #000000;"></span><span class="pun" style="color: #000000;">(:</span><span class="pln" style="color: #000000;">NEW</span><span class="pun" style="color: #000000;">.</span><span class="pln" style="color: #000000;">ACCOUNT</span><span class="pun" style="color: #000000;">,:</span><span class="pln" style="color: #000000;">NEW</span><span class="pun" style="color: #000000;">.</span><span class="pln" style="color: #000000;">GROUP_</span><span class="pun" style="color: #000000;">,:</span><span class="pln" style="color: #000000;">NEW</span><span class="pun" style="color: #000000;">.</span><span class="pln" style="color: #000000;">TYPE</span><span class="pun" style="color: #000000;">)</span><span class="pln" style="color: #000000;"> </span>
<span class="pln" style="color: #000000;"></span><span class="kwd" style="color: #00008b;">WHERE</span><span class="pln" style="color: #000000;"> ACNTGROUPS</span><span class="pun" style="color: #000000;">.</span><span class="pln" style="color: #000000;">TYPE </span><span class="pun" style="color: #000000;">=</span><span class="pln" style="color: #000000;"> </span><span class="str" style="color: #800000;">'1'</span><span class="pln" style="color: #000000;"> </span>
<span class="pln" style="color: #000000;"></span><span class="kwd" style="color: #00008b;">END</span><span class="pun" style="color: #000000;">;</span>

Everything works great without the where clause. Can an 'after insert' trigger have a condition clause? does my code above need reformating? thanks in advance.

Tagged:
user5786259

Answers

  • Pk
    Pk Member Posts: 712 Silver Badge
    edited Jun 5, 2015 11:02AM

    Remove the where clause.

    This is how insert values statement should be.

    INSERT INTO table_name

    VALUES (value1,value2,value3,...)

  • user8987217
    user8987217 Member Posts: 3
    edited Jun 5, 2015 11:08AM

    Would the code below improved? i need to have a condition clause somewhow.

    CREATE OR REPLACE TRIGGER  TRG_ACCT_IDW  

    AFTER INSERT  

    ON ACNTGROUPS 

    FOR EACH ROW  

    BEGIN  

        IF :NEW.TYPE = '1' then

            INSERT INTO IDWORKS_HC  

            (ACCOUNT,IDW_CATEGORYNO,GRTYPE)  

            VALUES  

            (:NEW.ACCOUNT,:NEW.GROUP_,:NEW.TYPE)  

        END IF;

    END;

    user5786259
  • Pk
    Pk Member Posts: 712 Silver Badge
    edited Jun 5, 2015 11:12AM

    Why do you want the where clause, what are you trying to achive.

    When you are using INSERT VALUES, then where clause cannot be used.

    If you want to use where clause, then use where in SELECT.

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited Jun 5, 2015 11:13AM

    That looks more like what you are looking for, yes.

    You could also use a when clause in the trigger - perhaps more efficient.

  • Pk
    Pk Member Posts: 712 Silver Badge
    edited Jun 5, 2015 11:33AM

    As suggested by John, WHEN clause can be used. You can change the condition if required.

    Example:

    FOR EACH ROW

    WHEN (new.IDW_CATEGORYNO != old.IDW_CATEGORYNO)

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited Jun 5, 2015 11:40AM

    If you're going to give an example, why not give an example that matches the requirement?

    after insert on acntgroups

    for each row

    when new.type = '1'...

  • user8987217
    user8987217 Member Posts: 3
    edited Jun 5, 2015 11:46AM

    Would 'when' clause work for after-insert trigger? is the below code syntax correct?

    CREATE OR REPLACE TRIGGER  TRG_ACCT_IDW  

    AFTER INSERT  

    ON ACNTGROUPS 

    FOR EACH ROW 

    WHEN (ACNTGROUPS.TYPE = '1')

    BEGIN  

      UPDATE IDWORKS_HC

            SET account=:new.account,

            IDW_CATEGORYNO = :NEW.GROUP_,

            GRTYPE = :NEW.TYPE

    END;

  • Pk
    Pk Member Posts: 712 Silver Badge
    edited Jun 5, 2015 11:46AM

    Yeah John thanks.

  • Unknown
    edited Jun 5, 2015 11:49AM

    If that trigger is ONLY used for inserting to another table then there is no need to fire it unless you want that insert to occur.

    See the Oracle docs for how to use the WHEN clause of a trigger

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm

    FOR EACH ROW
    Creates the trigger as a row trigger. The database fires a row trigger for each row that is affected by the triggering statement and meets the optional trigger constraint defined in the WHEN condition
    . . . WHEN (condition) Specifies a SQL condition that the database evaluates for each row that the triggering statement affects. If the value of <span class="codeinlineitalic">condition</span> is TRUE for an affected row, then <span class="codeinlineitalic">trigger_body</span> runs for that row; otherwise, <span class="codeinlineitalic">trigger_body</span> does not run for that row. The triggering statement runs regardless of the value of <span class="codeinlineitalic">condition</span>. The <span class="codeinlineitalic">condition</span> can contain correlation names (see "referencing_clause ::="). In <span class="codeinlineitalic">condition</span>, do not put a colon (:) before the correlation name NEW, OLD, or PARENT (in this context, it is not a placeholder for a bind variable).

    Just put that 'where' condition into the WHEN clause instead. See example 9-3 in the doc

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#CIHHEJCD

    Example 9-3 creates a conditional trigger that prints salary change information whenever a DELETE, INSERT, or UPDATE statement affects the EMPLOYEES table—unless that information is about the President. The database evaluates the WHEN condition for each affected row. If the WHEN condition is TRUE for an affected row, then the trigger fires for that row before the triggering statement runs. If the WHEN condition is not TRUE for an affected row, then trigger does not fire for that row, but the triggering statement still runs.
    
    Example 9-3 Conditional Trigger Prints Salary Change Information
    CREATE OR REPLACE TRIGGER print_salary_changes
      BEFORE DELETE OR INSERT OR UPDATE ON employees
      FOR EACH ROW
      WHEN (NEW.job_id <> 'AD_PRES')  -- do not print information about President
    DECLARE
      sal_diff  NUMBER;
    BEGIN
      sal_diff  := :NEW.salary  - :OLD.salary;
      DBMS_OUTPUT.PUT(:NEW.last_name || ': ');
      DBMS_OUTPUT.PUT('Old salary = ' || :OLD.salary || ', ');
      DBMS_OUTPUT.PUT('New salary = ' || :NEW.salary || ', ');
      DBMS_OUTPUT.PUT_LINE('Difference: ' || sal_diff);
    END;
    /

    See that 'WHEN' condition?

    See that it does NOT use a colon with the NEW correlation name?

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited Jun 5, 2015 12:05PM

    > is the below code syntax correct?

    no.

This discussion has been closed.