Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187.1K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 443 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
AFTER INSERT TRIGGER with Condition

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.
Answers
-
Remove the where clause.
This is how insert values statement should be.
INSERT INTO table_name
VALUES (value1,value2,value3,...)
-
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;
-
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.
-
That looks more like what you are looking for, yes.
You could also use a when clause in the trigger - perhaps more efficient.
-
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)
-
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'...
-
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;
-
Yeah John thanks.
-
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>
isTRUE
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 nameNEW
,OLD
, orPARENT
(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?
-
> is the below code syntax correct?
no.