AFTER INSERT TRIGGER with Condition

user8987217

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.

Pk

Remove the where clause.

This is how insert values statement should be.

INSERT INTO table_name

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

user8987217

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;

Pk

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

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

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

Pk

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

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

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

Yeah John thanks.

Unknown

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

> is the below code syntax correct?

no.