Hi, I'm new to triggers and I want to be able to enforce a somewhat complex rule on a table. Here is the definition for table 'xyz':
id_node int not null primary key,
parentid int not null,
nodetype smallint not null,
nodetext varchar(20) not null
The rules I want to enforce are the following:
(1) For each insert on this table, the first record inserted MUST have nodetype = 0. Any subsequent rows inserted must have a nodetype > 0. This will enforce the rule that there should exist only one row in the table with nodetype = 0.
(2) If rule 1 has been satisfied, then an insert on this table should fail if and only if the parentid column does not reference an id_node already in the table.
The effect, if you haven't noticed already, is to represent a tree-structure in the table. So essentially, you start with inserting the root node (nodetype = 0), and then traverse the tree structure in order....inserting each node along the way.
I have a (non-tested) start to a trigger definition here:
create trigger trig_xyz
after insert on xyz
referencing new row as nrow
for each row mode db2sql
-- enforce rule 1
when (count(*) = 0) then
when nrow.nodetype<>0 then -- raise exception (first node inserted must be the root node)
-- enforce rule 2
case when nrow.nodetype=0 then -- raise exception (only one root node allowed!)
when not exists(select id_node from xyz) then -- raise exception (parentid must reference a node in this table)
....SOMETHING like that. Any help would be greatly appreciated!