Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 



TRIGGER Example

Description

This section covers trigger definitions in the demo database. The triggers created in the demodb database are not complex, but use most of the features available in CUBRID. If you want to maintain the original state of the demodb database when testing such triggers, you must perform a rollback after changes are made to the data.

Triggers created by the user in the own database can be as powerful as applications created by the user.

Example 1

The following trigger created in the participant table rejects an update to the medal column (gold, silver, bronze) if a given value is smaller than 0. The evaluation time must be BEFORE because a correlation name new is used in the trigger condition. Although not described, the action time of this trigger is also BEFORE.

CREATE TRIGGER medal_trigger

BEFORE UPDATE ON participant

IF new.gold < 0 OR new.silver < 0 OR new.bronze < 0

EXECUTE REJECT; 

The medal_trigger trigger starts when the number of gold medals of the country whose nation code is 'BLA' is updated. Since a negative value is not permitted for the number of gold medals as shown above, this update is not allowed.

UPDATE participant

SET gold = -10

WHERE nation_code = 'BLA';

Example 2

The following trigger has the same condition as the one above except that STATUS INACTIVE is added. If the STATUS statement is omitted, the default value is ACTIVE. You can change the status to INACTIVE by using the ALTER TRIGGER statement.

You can specify whether or not to execute the trigger depending on the STATUS value.

CREATE TRIGGER medal_trig

STATUS ACTIVE

BEFORE UPDATE ON participant

IF new.gold < 0 OR new.silver < 0 OR new.bronze < 0

EXECUTE REJECT;

 

ALTER TRIGGER medal_trig

STATUS INACTIVE;

Example 3

The following trigger shows how integrity constraint is enforced when a transaction is committed. This example is different from the previous ones, in that one trigger can have specific conditions for multiple tables.

CREATE TRIGGER check_null_first

BEFORE COMMIT

IF 0 < (SELECT count(*) FROM athlete WHERE gender IS NULL)

OR 0 < (SELECT count(*) FROM game WHERE nation_code IS NULL)

EXECUTE REJECT;

Example 4

The following trigger delays the update integrity constraint check for the record table until the transaction is committed. Since the DEFERRED keyword is given as the event time, the trigger is not executed at the time.

CREATE TRIGGER deferred_check_on_record

DEFERRED UPDATE ON record

IF obj.score = '100'

EXECUTE INVALIDATE TRANSACTION;

Once completed, the update in the record table can be confirmed at the last point (commit or rollback) of the current transaction. The correlation name old cannot be used in the conditional clause of the trigger where DEFERRED UPDATE is used. Therefore, you cannot create a trigger as the following.

CREATE CLASS foo (n int);

CREATE TRIGGER foo_trigger

    DEFERRED UPDATE ON foo

    IF old.n = 100

    EXECUTE PRINT 'foo_trigger';

If you try to create a trigger as shown above, an error message is displayed and the trigger fails.

ERROR: Error compiling condition for 'foo_trigger' : old.n is not defined

The correlation name old can be used only with AFTER.