<p><a href="README.htm">Demos and Examples</a></p>

<h1 id="test-a-trigger">Test a Trigger</h1>

<hr />

<h2 id="triggers">Triggers</h2>

<p>There are many kinds of triggers. All of them use PL/SQL to define actions taken when the trigger is activated.</p>

<p>The &quot;Database PL/SQL Language Reference&quot; (11.2) <a href="https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#BABBJHHG">groups triggers</a> this way:</p>

<li>Simple DML Trigger</li>

<li>Compound DML Trigger</li>

<li>Instead of DML Trigger</li>

<li>System Trigger</li>

<p><a href="https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#BABBJHHG">Simple DML Triggers</a>:</p>

<tr class="header">

<th>Before</th>

<th>After</th>

<tr class="odd">

<td>Before Delete</td>

<td>After Delete</td>

<tr class="even">

<td>Before Insert</td>

<td>After Insert</td>

<tr class="odd">

<td>Before Update</td>

<td>After Update</td>

<p><a href="https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#BABDFIFA">Compound DML Triggers</a>:</p>

<tr class="header">

<th>Before</th>

<th>Instead of</th>

<th>After</th>

<tr class="odd">

<td>Each Row Deleted</td>

<td>Each Row Deleted</td>

<td>Each Row Deleted</td>

<tr class="even">

<td>Each Row Inserted</td>

<td>Each Row Inserted</td>

<td>Each Row Inserted</td>

<tr class="odd">

<td>Each Row Updated</td>

<td>Each Row Updated</td>

<td>Each Row Updated</td>

<tr class="even">

<td>Delete Statement</td>

<td>Delete Statement</td>

<tr class="odd">

<td>Insert Statement</td>

<td>Insert Statement</td>

<tr class="even">

<td>Update Statement</td>

<td>Update Statement</td>

<p><a href="https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#CIHEIGBE">Instead of DML Triggers</a>:</p>

<tr class="header">

<th>Instead of</th>

<tr class="odd">

<td>Instead of Delete</td>

<tr class="even">

<td>Instead of Insert</td>

<tr class="odd">

<td>Instead of Update</td>

<p><a href="https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#BABHEFGE">System Triggers</a>:</p>

<tr class="header">

<th>Before</th>

<th>Instead of</th>

<th>After</th>

<tr class="odd">

<td>Alter Statement</td>

<td>Alter Statement</td>

<td>Alter Statement</td>

<tr class="even">

<td>Analyze Statement</td>

<td>Analyze Statement</td>

<td>Analyze Statement</td>

<tr class="odd">

<td>Associate Statistics</td>

<td>Associate Statistics</td>

<td>Associate Statistics</td>

<tr class="even">

<td>Audit Statement</td>

<td>Audit Statement</td>

<td>Audit Statement</td>

<tr class="odd">

<td>Comment Statement</td>

<td>Comment Statement</td>

<td>Comment Statement</td>

<tr class="even">

<td>Create Statement</td>

<td>Create Statement</td>

<td>Create Statement</td>

<tr class="odd">

<td>Disassociate Statistics</td>

<td>Disassociate Statistics</td>

<td>Disassociate Statistics</td>

<tr class="even">

<td>Drop Statement</td>

<td>Drop Statement</td>

<td>Drop Statement</td>

<tr class="odd">

<td>Grant Statement</td>

<td>Grant Statement</td>

<td>Grant Statement</td>

<tr class="even">

<td>Noaudit Statement</td>

<td>Noaudit Statement</td>

<td>Noaudit Statement</td>

<tr class="odd">

<td>Rename Statement</td>

<td>Rename Statement</td>

<td>Rename Statement</td>

<tr class="even">

<td>Revoke Statement</td>

<td>Revoke Statement</td>

<td>Revoke Statement</td>

<tr class="odd">

<td>Truncate Statement</td>

<td>Truncate Statement</td>

<td>Truncate Statement</td>

<tr class="even">

<td>User Logoff</td>

<td>.</td>

<td>User Logon</td>

<tr class="odd">

<td>Database Shutdown</td>

<td>.</td>

<td>Database Startup</td>

<tr class="even">

<td>.</td>

<td>.</td>

<td>Servererror</td>

<tr class="odd">

<td>.</td>

<td>.</td>

<td>User Suspend</td>

<tr class="even">

<td>.</td>

<td>.</td>

<td>DB Role Change</td>

<p>For brevity, an example is provided for only one of these triggers. Db Role Change</p>

<h2 id="table-with-insert-trigger">Table with Insert Trigger</h2>

<p>Before a trigger an be created, a table must be created. The table will have a surrogate key, a natural key, and audit data.</p>

<p>Run this:</p>

<pre><code>create sequence trigger_test_seq;

create table trigger_test_tab

(id number constraint trigger_test_tab_nn1 not null

,name varchar2(30) constraint trigger_test_tab_nn2 not null

,created_dtm date constraint trigger_test_tab_nn3 not null

,constraint trigger_test_tab_pk primary key (id)

,constraint trigger_test_tab_uk1 unique (name)

);

<p>The trigger to be tested does 2 things</p>

<li>Populate the surrogate key, if needed.</li>

<li>Overwrite the audit data.</li>

<p>Run this:</p>

<pre><code>create or replace trigger trigger_test_bir

before insert on trigger_test_tab

for each row

begin

if :new.id is null

then

:new.id := trigger_test_seq.nextval;

end if;

:new.created_dtm := sysdate;

end;

/

<h2 id="create-a-simple-test-runner">Create a Simple Test Runner</h2>

<p>All test runners are written as a PL/SQL package. A simple package is created first. A DBOUT is also identified.</p>

<p>Run this:</p>

<pre><code>create or replace package trigger_test_pkg authid definer

as

procedure wtplsql_run;

end trigger_test_pkg;

/

<p>The trigger being tested is a table DML trigger. Testing of a table trigger like this requires a modification of the data in the table. The consequences of leaving this modified data after the test must be considered. In this test, the data modification will not be preserved.</p>

<p>This test case will only test a happy path.</p>

<p>Run this:</p>

<pre><code>create or replace package body trigger_test_pkg

as

procedure t_happy_path_1

is

l_rec trigger_test_tab%ROWTYPE;

begin

wt_assert.g_testcase := &#39;Constructor Happy Path 1&#39;;

-- This uncommitted DML will ROLLBACK if an exception is raised.

insert into trigger_test_tab (name) values (&#39;Test1&#39;)

returning id into l_rec.id;

wt_assert.isnotnull (

msg_in =&gt; &#39;l_rec.id&#39;,

check_this_in =&gt; l_rec.id);

select * into l_rec from trigger_test_tab where id = l_rec.id;

wt_assert.eq (

msg_in =&gt; &#39;l_rec.name&#39;,

check_this_in =&gt; l_rec.name,

against_this_in =&gt; &#39;Test1&#39;);

wt_assert.isnotnull (

msg_in =&gt; &#39;l_rec.created_dtm&#39;,

check_this_in =&gt; l_rec.created_dtm);

rollback;

end t_happy_path_1;

--% WTPLSQL SET DBOUT &quot;TRIGGER_TEST_BIR:TRIGGER&quot; %--

procedure wtplsql_run

is

begin

t_happy_path_1;

end wtplsql_run;

end trigger_test_pkg;

/

<h2 id="check-the-results">Check the results</h2>

<p>Run this:</p>

<pre><code>set serveroutput on size unlimited format word_wrapped

begin

wtplsql.test_run(&#39;TRIGGER_TEST_PKG&#39;);

wt_text_report.dbms_out(USER,&#39;TRIGGER_TEST_PKG&#39;,30);

end;

/

<p>And Get This:</p>

<pre><code> wtPLSQL 1.1.0 - Run ID 58: 23-Jun-2018 12:04:20 PM

Test Results for WTP_DEMO.TRIGGER_TEST_PKG

Total Test Cases: 1 Total Assertions: 3

Minimum Interval msec: 0 Failed Assertions: 0

Average Interval msec: 76 Error Assertions: 0

Maximum Interval msec: 228 Test Yield: 100.00%

Total Run Time (sec): 0.2

Code Coverage for TRIGGER WTP_DEMO.TRIGGER_TEST_BIR

Ignored Lines: 0 Total Profiled Lines: 5

Excluded Lines: 0 Total Executed Lines: 4

Minimum LineExec usec: 1 Not Executed Lines: 0

Average LineExec usec: 137 Unknown Lines: 1

Maximum LineExec usec: 326 Code Coverage: 100.00%

Trigger Source Offset: 3

- WTP_DEMO.TRIGGER_TEST_PKG Test Result Details (Test Run ID 58)

-----------------------------------------------------------

---- Test Case: Constructor Happy Path 1

PASS 228ms l_rec.id. ISNOTNULL - Expected NOT NULL and got &quot;15&quot;

PASS 0ms l_rec.name. EQ - Expected &quot;Test1&quot; and got &quot;Test1&quot;

PASS 0ms l_rec.created_dtm. ISNOTNULL - Expected NOT NULL and got &quot;23-JUN-2018 12:04:20&quot;

- WTP_DEMO.TRIGGER_TEST_BIR TRIGGER Code Coverage Details (Test Run ID 58)

Source TotTime MinTime MaxTime

Line Stat Occurs (usec) (usec) (usec) Text

------ ---- ------ --------- ------- --------- ------------

4 UNKN 0 11 11 11 begin

5 EXEC 1 216 216 216 if :new.id is null

7 EXEC 1 326 326 326 :new.id := trigger_test_seq.nextval;

9 EXEC 1 4 1 3 :new.created_dtm := sysdate;

10 EXEC 1 2 2 2 end;

<p>This is report level 30, the most detailed level of reporting. Starting from the top, we find the test runner executed 1 test case and 3 assertions. All tests passed for a 100% yield. The code coverage for the trigger shows 5 profiles, 4 executed, and a code coverage of 100%. Notice the trigger offset of 3 which aligns the source code with the profiled lines.</p>

<p>This is not a complete test. More test cases are needed to confirm various values are handled correctly when inserted.</p>

<hr />

<p><a href="README.htm">Demos and Examples</a></p>