[ALTER | CREATE] TRIGGER

Advantage SQL Engine

  This is the first topic This is the last topic  

Adds or modifies a trigger definition in the Advantage Data Dictionary

Syntax

[ALTER | CREATE] TRIGGER <trigger-name> ON <table-name> <trigger-type> <trigger-event> <trigger-container> [trigger-options]

 

trigger-type ::=

BEFORE | INSTEAD OF | AFTER | CONFLICT [ON]

trigger-event ::=

INSERT | UPDATE | DELETE

trigger-container ::=

<library-container> | <script-container>

library-container ::=

FUNCTION <function-name> IN <library-type> <library-name>

function-name ::=

A user-defined function name. Name of the function in the library that holds the trigger code.

library-type ::=

LIBRARY | ASSEMBLY

library-name ::=

A user-defined library name. If library-type is LIBRARY, this is the name of the Windows DLL or Linux shared object that contains the trigger function. If library-type is ASSEMBLY, this is the name of the COM ProgID or the .NET assembly that contains the trigger function. If the file name has any special characters, the name should be enclosed in double quotes or square brackets.

script-container ::=

BEGIN <statement> ; [, <statement> ; ] END

statement ::=

any valid SQL statement

trigger-options ::=

NO VALUES | NO MEMOS | NO TRANSACTION | PRIORITY <trigger-priority>

trigger-priority ::=

A user-defined integer values, specifying the triggers firing priority.

Remarks

CREATE TRIGGER and ALTER TRIGGER can only be called by a user with ALTER permissions on the associated table. The CREATE TRIGGER statement creates a new trigger for the table. The ALTER TRIGGER statement modifies the definition of an existing trigger.

Trigger creation or modification does not verify library or .NET assembly existence. If a trigger is defined on a library or assembly that does not exist, a run-time error will occur when the trigger is executed.

Statements inside script triggers are validated for syntactical correctness only. If any semantic errors exist, a run-time error will occur when the trigger is executed.

If a trigger container is in use, and modifications are made to a trigger definition (including trigger addition or deletion), the changes will not take effect until all users who have used a trigger in that container disconnect from the database.

Currently trigger creation and deletion will not affect tables already opened by the server. For example, if table1 is opened by active clients, and the administrator adds a new trigger to table1, the trigger will not activate until all active users have closed table1 and it has been re-opened.

See What is a Trigger? for detailed information.

Examples

CREATE TRIGGER mytrigger ON orders AFTER DELETE

BEGIN

INSERT INTO backup_orders SELECT * FROM __old;

END

 

 

CREATE TRIGGER mytrigger ON orders INSTEAD OF UPDATE

FUNCTION MyFunction IN ASSEMBLY [MyAssembly.MyClass] PRIORITY 68

 

 

CREATE TRIGGER mytrigger ON orders AFTER INSERT

FUNCTION MyFunction IN LIBRARY [mytriggers.dll]

 

 

CREATE TRIGGER mytrigger ON orders BEFORE INSERT

FUNCTION MyFunction IN ASSEMBLY [MyAssembly.MyClass]

NO VALUES PRIORITY 23

 

 

CREATE TRIGGER mytrigger ON orders AFTER DELETE

BEGIN

INSERT INTO backup_orders SELECT * FROM __old;

END

NO MEMOS PRIORITY 1

 

ALTER TRIGGER mytrigger ON orders AFTER DELETE

BEGIN

INSERT INTO log_orders_delete SELECT * FROM __old;

END

PRIORITY 2