CREATE TRIGGER statement 

The CREATE TRIGGER statement creates a trigger for a base table (see Table).

Syntax

<create trigger statement> ::= CREATE TRIGGER <trigger name> FOR <table name>
AFTER <trigger event,..> EXECUTE (<routine>) [WHENEVER <search condition> ]

<trigger event> :: INSERT | UPDATE [(<column list>)] | DELETE
<column list> ::= <column name> | <column list>; <column name>

trigger name, table name, search condition, routine, column name

The trigger ensures that the hotel number in the room table is also changed when a hotel number is changed in the hotel table.

CREATE TRIGGER hotel_update FOR hotel AFTER UPDATE EXECUTE (
TRY
  IF NEW.hno <> OLD.hno
  THEN UPDATE tours.room SET hno = NEW.hno WHERE hno = OLD.hno;
CATCH
  IF $rc <> 100
  THEN STOP ($rc, ‘Unexpected error‘);
)

Explanation

A trigger is a special type of database procedure that is assigned to a base table. This database procedure cannot be executed explicitly with the CALL statement, but rather automatically by SAP DB when defined events ( trigger events ) for the table occur.

SAP DB provides a language (special SQL syntax that has been extended to include variables, control structures, and troubleshooting measures) that can be used to define triggers.

The specified synonym name must identify an existing base table of the current user.

Trigger event

The trigger event defines what triggers the trigger. The trigger is always invoked if the triggering event has been processed correctly.

INSERT: the INSERT trigger event causes the trigger to be executed for each row inserted in the table.

UPDATE: the UPDATE event causes the trigger to be executed for each modification made to a row in the table. If a column list is specified, the trigger is only called if one of the columns in the column list was modified.

DELETE: the DELETE trigger event causes the trigger to be executed for every row deleted from the table.

A maximum of one trigger can be defined for each trigger event in each table.

Trigger routine

Each INSERT trigger implicitly has a corresponding variable NEW.<column name> for each column in the table. When the trigger is executed, this variable has the value of the corresponding column in the inserted row. NEW is optional.

Each UPDATE trigger implicitly has a corresponding variable NEW.<column name> and OLD.<column name> for each column in the table. When the trigger is executed, the OLD.<column name> variable has the value of the corresponding column in front of and NEW.<column name> after the change in the row. NEW is optional.

Each DELTE trigger implicitly has a corresponding variable OLD.<column name> for each column in the table. When the trigger is executed, this variable has the value of the corresponding column in the deleted row. OLD is optional.

See also:

routine

If the trigger is terminated by STOP with an error number not equal to zero, the entire SQL statement that triggered the trigger fails.

The SUBTRANS statement is not allowed in a trigger.

If a WHENEVER statement is specified, the trigger is only executed if the SEARCH condition is fulfilled. The condition must not contain a subquery or set function.