Declaring a template item in a trigger definition

Chapter 6 Building Triggers and Procedures

Declaring a template item in a trigger definition

Certain DBMS require that a cursor and variables are declared for each template item before the template item name is used in the script. This can be a statement that calls a corresponding procedure. You can use the following format to declare a template item:

Decl

template item name.

For example, the trigger definition for Oracle 8 contains the .DeclInsertChildParentExist statement which declares the following .InsertChildParentExist template item:

--  Before insert trigger "[%QUALIFIER%]%TRIGGER%" for table "[%QUALIFIER%]%TABLE%"
create trigger [%QUALIFIER%]%TRIGGER% before insert
on [%QUALIFIER%]%TABLE% for each row
declare
    integrity_error  exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
    .DeclInsertChildParentExist
begin
    .InsertChildParentExist
--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
/

In a generated trigger script, .DeclInsertChildExist corresponds to the following definition:

.FOREACH_PARENT()
--  Declaration of InsertChildParentExist constraint for the parent "[%PQUALIFIER%]%PARENT%"
.DEFINE "CURSOR" "cpk%REFNO%_%.25L:TABLE%"
cursor %CURSOR%(.JOIN("var_%.L26:FK% %.L:COLTYPE%", "", ",", ") is")
   select 1
   from   [%PQUALIFIER%]%PARENT%
   where  .JOIN("%PK% = var_%.L26:FK%", "and   ")
    and   .JOIN("var_%.L26:FK% is not null", "and   ", "", ";")
.ENDFOR