<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="9. SQL Procedure Language Guide" />
<meta name="dc.subject" content="9. SQL Procedure Language Guide" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="sqlprocedures.html" title="Chapter Contents" />
<link rel="prev" href="twopcimplementation.html" title="Distributed Transaction & Two Phase Commit" />
<link rel="next" href="charescaping.html" title="Character Escaping" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>9. SQL Procedure Language Guide</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="TRIGGERS" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>9. SQL Procedure Language Guide</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="sqlprocedures.html">Chapter Contents</a> | <a class="link" href="twopcimplementation.html" title="Distributed Transaction & Two Phase Commit">Prev</a> | <a class="link" href="charescaping.html" title="Character Escaping">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="sqlprocedures.html">SQL Procedure Language Guide</a>
</div>
<br />
<div>
<a href="GENERALPRINCIPLES.html">General Principles</a>
</div>
<div>
<a href="SCOPEOFDECLARATION.html">Scope of Declarations</a>
</div>
<div>
<a href="sqlplDATATYPES.html">Data Types</a>
</div>
<div>
<a href="RESULTSETS.html">Handling Result Sets</a>
</div>
<div>
<a href="ARRAYS.html">Result Sets and Array Parameters</a>
</div>
<div>
<a href="EXCEPTIONS.html">Exception Semantics</a>
</div>
<div>
<a href="PLREF.html">Virtuoso/PL Syntax</a>
</div>
<div>
<a href="EXECSQLPROCSELECT.html">Execute Stored Procedures via SELECT statement</a>
</div>
<div>
<a href="EXECSQLPROCFORK.html">Execute Stored Procedures In Background</a>
</div>
<div>
<a href="createassembly.html">CREATE ASSEMBLY Syntax - External Libraries</a>
</div>
<div>
<a href="createexthostproc.html">CREATE PROCEDURE Syntax - External hosted procedures</a>
</div>
<div>
<a href="ASYNCEXECMULTITHREAD.html">Asynchronous Execution and Multithreading in Virtuoso/PL</a>
</div>
<div>
<a href="PERFTIPS.html">Performance Tips</a>
</div>
<div>
<a href="PROCEDURES_TRANSACTIONS.html">Procedures and Transactions</a>
</div>
<div>
<a href="twopcimplementation.html">Distributed Transaction & Two Phase Commit</a>
</div>
<div class="selected">
<a href="TRIGGERS.html">Triggers</a>
<div>
<a href="#createtrigger" title="The CREATE TRIGGER statement">The CREATE TRIGGER statement</a>
<a href="#triggers_on_views" title="Triggers on Views">Triggers on Views</a>
<a href="#droptrigger" title="The DROP TRIGGER statement">The DROP TRIGGER statement</a>
<a href="#triggers_vdb" title="Triggers and Virtual Database">Triggers and Virtual Database</a>
</div>
</div>
<div>
<a href="charescaping.html">Character Escaping</a>
</div>
<div>
<a href="PLSCROLLCRSRS.html">Virtuoso/PL Scrollable Cursors</a>
</div>
<div>
<a href="plmodules.html">Virtuoso PL Modules</a>
</div>
<div>
<a href="handlingplcondit.html">Handling Conditions In Virtuoso/PL Procedures</a>
</div>
<div>
<a href="pldebugger.html">Procedure Language Debugger</a>
</div>
<div>
<a href="rowlevelsecurity.html">Row Level Security</a>
</div>
<br />
</div>
<div id="text">
<a name="TRIGGERS" />
<h2>9.16. Triggers</h2>
<p>A trigger is a procedure body associated with a table and an event.
A trigger can take effect before, after or instead of the event on the subject
table. Several before, after or instead of triggers may exist for a given event
on a given table, which can be fired in a specified order. </p>
<p>Triggers are useful for enforcing integrity rules, maintaining the
validity of data computed from other data, accumulating history data etc.
</p>
<p>
A trigger body has no arguments in the sense a procedure does. A trigger
body implicitly sees the columns of the subject table as read-only
parameters. An update trigger may see both the new and old values of
the row of the subject table. These are differentiated by correlation
names in the REFERENCING clause.
</p>
<p>
Triggers are capable of cascading; the code of a trigger may cause
another trigger to be activated. This may lead to non-terminating
recursion in some cases. Triggers may be turned off either inside a
compound statement or inside a connection with the SET TRIGGERS OFF
statement.</p>
<p>
An update trigger may have a set of sensitive columns whose update will
cause the trigger code to be run. Update of non-sensitive columns will
not invoke the trigger. If no column list is specified any update will
invoke the trigger.
</p>
<a name="createtrigger" />
<h3>9.16.1. The CREATE TRIGGER statement</h3>
<p>Triggers can be defined to act upon a table or column and fire upon:</p>
<ul>
<li>UPDATE</li>
<li>INSERT</li>
<li>DELETE</li>
</ul>
<p>at the following times during the operation on a table or column:</p>
<ul>
<li>BEFORE</li>
<li>AFTER</li>
<li>INSTEAD OF</li>
</ul>
<p>Triggers have a unique name which is qualified by the current catalog
and owner. The trigger name is only really relevant for the
purposes of dropping triggers. Triggers operate on a table or column which
must be adequately qualified.</p>
<p>The trigger body has read-only access to the values of the data
manipulation operation that triggered the trigger. In the case of an update
statement it has access to both old and new values for each effected column.
These values cannot be changed directly. If the trigger is to influence any
data in a table, even from the current operation, it must be achieved by
another SQL statement. The REFERENCING clause allows specifying a
correlation name for new and old values of columns. By default, the new
values are seen under the column names without a correlation name. If old
values of updated columns are needed, the REFERENCING OLD AS <alias>
will make <alias>.<column> refer to the old value.</p>
<p>Triggers defined to make further operations within the same or other
table may fire further triggers, or even the same trigger again. Care must be
taken to understand the implications of this and when triggers cane be
allowed to continue firing after the current trigger. For example, an after update
trigger that makes a further update to the same table will fire the same trigger
again and may continue looping in this way endlessly.
The <span class="computeroutput">SET TRIGGER</span> statement can be
issued to control this:</p>
<ul>
<li>SET TRIGGERS on; -- (default state) further triggers within this transaction are allowed to fire.</li>
<li>SET TRIGGERS off; -- further triggers within this transaction are disabled.</li>
</ul>
<p>A table may have more than one trigger. Their execution order can be
specified using the ORDER clause. Each trigger gets an order number, triggers
are called starting at the lowest order number in ascending order.</p>
<p>Syntax:</p>
<div>
<pre class="programlisting">
CREATE TRIGGER NAME action_time event ON q_table_name
opt_order opt_old_ref trig_action
action_time
: BEFORE
| AFTER
event
: INSERT
| UPDATE opt_column_commalist
| DELETE
opt_order
| ORDER INTNUM
opt_old_ref
| REFERENCING old_commalist
trig_action
: compound_statement
old_commalist
: old_alias
| old_commalist ',' old_alias
old_alias
: OLD AS NAME
| NEW AS NAME
</pre>
</div>
<a name="ex_createtrigger" />
<div class="example">
<div class="exampletitle">Creating a simple trigger</div>
<p>This trigger is a simple example of one that would cause an
endless loop if further triggering were not disabled.</p>
<div>
<pre class="programlisting">
create trigger update_mydate after
update on mytable referencing old as O, new as N
{
set triggers off;
update mytable
set
previousdate = O.mydate,
mydate=now()
where id=N.id;
}
;
</pre>
</div>
<p>The trigger makes aliases for the values of the column that are part of
the SQL manipulation transaction that will be in progress, hence the values of the
columns can be accessed as "O.column" and "N.column" for old and new values respectively.</p>
<p>The set statement is scope to the procedure or trigger body where it occurs, plus procedures called from there , thus when the trigger
finishes no other triggers are effected by it.</p>
</div>
<a name="ex_createtriggerinsteadof" />
<div class="example">
<div class="exampletitle">Creating a simple trigger using INSTEAD OF</div>
<p>This trigger example will show how INSTEAD OF can be used to intercept
the values of an insert statement and re-write it. In this case the purpose is
to deliberately truncate VARCHAR inserts to prevent an error if the
data type bounds are exceeded:</p>
<p>First we create a test table with a 30 character limitation in one of the columns:</p>
<div>
<pre class="programlisting">
SQL>create table test_trunc (
id integer not null primary key,
txt varchar (30)
)
;
Done. -- 10 msec.
</pre>
</div>
<p>Then we attempt to insert 33 characters into it with the following results:</p>
<div>
<pre class="programlisting">
SQL>insert into test_trunc (id, txt)
values (1, 'aaaaaaaaaabbbbbbbbbbccccccccccxxx');
*** Error 22026: [Virtuoso ODBC Driver][Virtuoso Server]SR319: Max column length (30) of column [txt] exceeded
</pre>
</div>
<p>Now we make a trigger to fire instead of insert statements that can
perform some custom error correction, in this case we simply want to chop-off
any extra characters that will cause an insert to fail.</p>
<div>
<pre class="programlisting">
SQL>create trigger test_trunc_it
instead of insert on test_trunc
referencing new as N
{
set triggers off; -- we do not want this looping...
insert into test_trunc (id, txt) values (N.id, left(N.txt, 30));
}
;
Done. -- 10 msec.
</pre>
</div>
<p>We perform the same test insert, now without errors:</p>
<div>
<pre class="programlisting">
SQL>insert into test_trunc (id, txt)
values (1, 'aaaaaaaaaabbbbbbbbbbccccccccccxxx');
Done. -- 10 msec.
</pre>
</div>
<p>And to see what we have in the database, a quick select:</p>
<div>
<pre class="programlisting">
SQL> select * from test_trunc;
id txt
INTEGER NOT NULL VARCHAR
______________________________________________________
1 aaaaaaaaaabbbbbbbbbbcccccccccc
1 Rows. -- 20 msec.
</pre>
</div>
</div>
<br />
<a name="triggers_on_views" />
<h3>9.16.2. Triggers on Views</h3>
<p>In virtuoso you can create a trigger on a view. To accomplish this there is only one condition: The first trigger for a given type of event (INSERT/DELETE/UPDATE)
must be an INSTEAD OF trigger. After such a trigger is defined then any type of triggers (AFTER/BEFORE) can be added.</p>
<a name="ex_createtriggeronview" />
<div class="example">
<div class="exampletitle">Creating a trigger on view</div>
<p>We will make two tables and an union view for them. Then we will create a trigger which inserts a new record in one of the tables according to values.</p>
<p>First lets create the tables and the view.</p>
<div>
<pre class="programlisting">
create table first_table(
id integer not null primary key,
txt varchar
);
create table second_table(
id integer not null primary key,
txt varchar
);
create view all_tables (id,from_table,txt)
as select id,'first',txt from first_table
union all
select id,'second',txt from second_table;</pre>
</div>
<p>Now lets create a trigger instead of insert for the view and insert some data.</p>
<div>
<pre class="programlisting">
create trigger insert_all_tables
instead of insert on all_tables referencing new as N{
if(N.from_table = 'first' or N.from_table = 'all')
insert into first_table (id,txt) values(N.id,N.txt);
if(N.from_table = 'second' or N.from_table = 'all')
insert into second_table (id,txt) values(N.id,N.txt);
};
insert into all_tables (id,from_table,txt) values (1,'first','into first');
insert into all_tables (id,from_table,txt) values (2,'second','into second');
insert into all_tables (id,from_table,txt) values (3,'all','into all');
select * from all_tables;
id from_table txt
INTEGER VARCHAR VARCHAR
_______________________________________________________________________________
1 first into first
3 first into all
2 second into second
3 second into all
</pre>
</div>
</div>
<p>You can see that the trigger inserted the data in the two tables according the value of from_table.</p>
<br />
<a name="droptrigger" />
<h3>9.16.3. The DROP TRIGGER statement</h3>
<div>
<pre class="programlisting">
DROP TRIGGER qualified_name
</pre>
</div>
<p>This drops a trigger of the given name. The name may optionally have
a qualifier and owner, in which case these should be the qualifier and
owner of the subject table of the trigger. Identical trigger
names may exist for identically named tables in different namespaces.
</p>
<br />
<a name="triggers_vdb" />
<h3>9.16.4. Triggers and Virtual Database</h3>
<p>
Triggers may be defined on tables residing on remote databases. The
semantic of triggers is identical but will of course only take place when
the manipulation takes place through the Virtuoso defining the triggers.
Trigger bodies may reference remote tables just as any other procedure
bodies can. Note that triggers can be used for replication, i.e. one may
define a local change to be mirrored to a remote table using a trigger.
</p>
<p>
Consider an application with a warehouse supplying orders. There is a
total value of all orders kept at the warehouse level and there is the
total value of all order lines kept at the order level. When an order
line is added, both the order value and consequently the total order
value are updates. These values are maintained for insert, update and
delete of order line. On the other have, when an order is deleted,
all corresponding order lines must be deleted.
</p>
<p>
These rules are maintained with the below set of triggers.
</p>
<div>
<pre class="screen">
drop table T_WAREHOUSE;
drop table T_ORDER;
drop table T_ORDER_LINE;
create table T_WAREHOUSE (W_ID integer default 1,
W_ORDER_VALUE float default 0,
W_DATA varchar,
primary key (W_ID));
create table T_ORDER (O_ID integer not null primary key, O_C_ID integer,
O_W_ID integer default 1,
O_VALUE numeric default 0,
O_MODIFIED datetime);
create table T_ORDER_LINE (OL_O_ID integer,
OL_I_ID integer,
OL_QTY integer,
OL_MODIFIED timestamp,
OL_I_PRICE float default 1,
primary key (OL_O_ID, OL_I_ID));
create index OL_I_ID on T_ORDER_LINE (OL_I_ID);
create trigger AMT_INS after insert on T_ORDER_LINE
{
update T_ORDER
set O_VALUE = O_VALUE + OL_QTY * OL_I_PRICE
where O_ID = OL_O_ID;
}
create trigger AMT_DEL after delete on T_ORDER_LINE
{
update T_ORDER
set O_VALUE = O_VALUE - OL_QTY * OL_I_PRICE
where O_ID = OL_O_ID;
}
create trigger AMT before update on T_ORDER_LINE referencing old as O
{
update T_ORDER
set O_VALUE = O_VALUE - O.OL_QTY * O.OL_I_PRICE + OL_QTY * OL_I_PRICE
where O_ID = OL_O_ID;
}
create trigger W_VALUE before update (O_VALUE) on T_ORDER
referencing old as O, new as N
{
update T_WAREHOUSE
set W_ORDER_VALUE = W_ORDER_VALUE - O.O_VALUE + N.O_VALUE
where W_ID = O.O_W_ID;
}
create trigger O_DEL_OL after delete on T_ORDER order 2
{
set triggers off;
delete from T_ORDER_LINE where OL_O_ID = O_ID;
}
create trigger O_DEL_W after delete on T_ORDER order 1
{
update T_WAREHOUSE
set W_ORDER_VALUE = W_ORDER_VALUE - O_VALUE
where W_ID = O_W_ID;
}
create procedure ol_reprice_1 (in i_id integer, in i_price float)
{
declare id integer;
declare cr cursor for
select OL_I_ID from T_ORDER_LINE;
whenever not found goto done;
open cr;
while (1) {
fetch cr into id;
if (id = i_id)
update T_ORDER_LINE set OL_I_PRICE = i_price where current of cr;
}
done:
return;
}
create procedure ol_reprice_2 (in i_id integer, in i_price float)
{
declare id integer;
declare cr cursor for
select OL_I_ID from T_ORDER_LINE order by OL_I_ID;
whenever not found goto done;
open cr;
while (1) {
fetch cr into id;
if (id = i_id)
update T_ORDER_LINE set OL_I_PRICE = i_price where current of cr;
}
done:
return;
}
create procedure ol_del_i_id_2 (in i_id integer)
{
declare id integer;
declare cr cursor for
select OL_I_ID from T_ORDER_LINE order by OL_I_ID;
whenever not found goto done;
open cr;
while (1) {
fetch cr into id;
if (id = i_id)
delete from T_ORDER_LINE where current of cr;
}
done:
return;
}
</pre>
</div>
<div class="note">
<div class="notetitle">Compatibility:</div>
<p>Virtuoso triggers are modeled after SQL 3. Omitted are the FOR EACH
STATEMENT and related OLD TABLE AS phrases as well as the WHEN in the
trigger body. The implementation is otherwise complete.
</p>
</div>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="twopcimplementation.html" title="Distributed Transaction & Two Phase Commit">Previous</a>
<br />Distributed Transaction & Two Phase Commit</td>
<td align="center" width="34%">
<a href="sqlprocedures.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="charescaping.html" title="Character Escaping">Next</a>
<br />Character Escaping</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
49
