Upon operation on a specified table (INSERT, UPDATE, and DELETE statements), defines an action (trigger) that automatically executes SQL statements.
- [authorization-identifier.]trigger-identifier
- authorization-identifier
- Specifies the authorization identifier of the owner of the trigger being defined. The default is the authorization identifier of the user who executes CREATE TRIGGER.
- trigger-identifier
- Specifies the name of the trigger being defined.
- trigger-action-time::={BEFORE|AFTER}
- BEFORE
- Executes the trigger action before an operation is performed on the table.
- If BEFORE is specified, function calls other than data update SQL statements (INSERT, UPDATE, or DELETE statements), the CALL statement, and the default constructor function cannot be specified in trigger-SQL-statement.
- The trigger for which BEFORE is specified in trigger-action-time is called a BEFORE trigger.
- AFTER
- Executes the trigger action after an operation is performed on the table.
- The trigger for which AFTER is specified in trigger-action-time is called an AFTER trigger.
- trigger-event::={INSERT|DELETE|UPDATE[OF column-name[, column-name]...]}
Specifies the type of operation that induces the execution of the trigger.
- INSERT
- Executes a trigger when a row is inserted into the table. The trigger for which INSERT is specified in trigger-event is called an INSERT trigger.
- If INSERT is specified in trigger-event, OLD[ROW][AS] old-values-correlation-name cannot be specified in old-or-new-values-alias.
- DELETE
- Executes a trigger when a row is deleted from the table. The trigger for which DELETE is specified in trigger-event is called a DELETE trigger.
- If DELETE is specified in trigger-event, NEW[ROW][AS] new-values-correlation-name cannot be specified in old-or-new-values-alias.
- UPDATE
- Executes a trigger when a row is updated in the table. The trigger for which UPDATE is specified in trigger-event is called an UPDATE trigger.
- If UPDATE is specified in trigger-event, a trigger is executed even if the value does not change before and after the update, provided that the trigger action conditions are satisfied.
- OF column-name[, column-name]...
- For executing a trigger upon updating of a specific column, specify OF column-name[, column-name].... The columns specified here are called trigger event columns.
- The following rules apply to trigger event columns:
- In column-name, specify the column name of the table for which a trigger is defined.
- Column names cannot be specified in duplicate.
- If a repetition column is specified, a trigger is executed when an UPDATE statement containing only an ADD or DELETE clause is executed for the column.
- If a trigger event column is omitted, the default is all column names for the target table (including columns that are added after the trigger is defined). The trigger is executed when an UPDATE statement containing only an ADD or DELETE clause is executed.
- [authorization-identifier.]table-identifier
Specifies the table name of the base table for which a trigger is being defined.
A user can define a trigger only for the tables that he or she owns. A trigger cannot be defined for a view table, foreign table, or other RD node tables.
- REFERENCING old-or-new-values-alias-list::=old-or-new-values-alias[old-or-new-values-alias]
When referencing a row before and after an update as part of a trigger definition, specifies an alias.
In old-or-new-values-alias, either OLD[ROW][AS] old-values-correlation-name or NEW[ROW][AS]new-values-correlation-name can be specified only once.
- old-or-new-values-alias::={OLD [ROW] [AS] old-values-correlation-name|NEW [ROW] [AS] new-values-correlation-name}
old-values-correlation-name::=correlation-name
new-values-correlation-name::=correlation-name
This option is specified when referencing a row either before or after updating by assigning a name to it.
The following rules apply to old or new values aliases:
- ROW and AS have the same effect, irrespective of whether or not they are specified.
- The same correlation name cannot be specified in both old-values-correlation-name and new-values-correlation-name.
- The scope of a correlation name specified in either old-values-correlation-name or new-values-correlation-name is the entire trigger definition.
- If a column qualified with new-values-correlation-name is updated by using a BEFORE trigger, the update takes effect in the table. However, a BEFORE trigger, cannot update any of the following columns qualified with new-values-correlation-name; an attempt to update such a column may produce a runtime error:
- Columns for which SYSTEM GENERATED is specified
The BEFORE trigger for which the trigger event is INSERT cannot update any of the following columns by qualifying them with new-values-correlation-name; an attempt to update such a column may produce a runtime error:
- Columns that are specified in the partitioning key for a partitioned table (exclusive of flexible hash-partitioned tables)
For the BEFORE trigger, even when the updating is performed by qualifying a name with new-values-correlation-name, the insertion value (trigger event: INSERT) or the update value (trigger event: UPDATE) before updating must be a value that can be inserted into a specified column or a value that can be used for updating the column. For example, NULL cannot be specified as an insertion or update value before updating by means of a trigger action if the specified column is NOT NULL-constrained. With regard to uniqueness constraints, however, insertion and updating can be performed if the value that is updated by a trigger action satisfies the uniqueness constraint.
- Repetition columns and abstract data-type columns cannot be referenced by qualifying them with new-values-correlation-name and old-values-correlation-name (in a trigger definition, repetition columns and abstract data-type columns cannot be referenced in the table in which a trigger is defined).
- ROW cannot be specified in old-values-correlation-name or in new-values-correlation-name.
- OLD [ROW] [AS] old-values-correlation-name
- This operand is specified when referencing a row, before updating it, by assigning a name to it.
- The value stored in the column qualified with old-values-correlation-name is a value that was in effect before the SQL statement that caused the trigger was executed. For the UPDATE statement, it is a pre-update value; for the DELETE statement, it is the column value of the row to be deleted.
- NEW [ROW] [AS] new-values-correlation-name
- This operand is specified when referencing a row, after updating it, by assigning a name to it.
- The value stored in the column qualified with new-values-correlation-name is a value that is the result of execution of the SQL statement that caused the trigger. For the UPDATE statement, it is a post-update value; for the INSERT statement, it is the inserted value. If, however, a column qualified with new-values-correlation-name is updated during the trigger event, the updated value is inherited.
- trigger-action::=
[{FOR EACH ROW|FOR EACH STATEMENT}]
[WHEN (search-condition)]
trigger-SQL-statement
- FOR EACH ROW
- This option is specified when executing a trigger by updated row. The trigger for which FOR EACH ROW is specified in trigger-action is called a trigger by row. The FOR EACH ROW option causes the execution of the trigger each time the one row in the table is updated.
- FOR EACH STATEMENT
- This option is specified when executing a trigger by SQL statement. The trigger for which FOR EACH STATEMENT is specified in trigger-action is called a trigger by statement.
- FOR EACH STATEMENT causes the execution of the trigger by each SQL statement, in which case the trigger is executed even where there are no rows to be updated.
- FOR EACH STATEMENT cannot be specified together with old-or-new-values-alias-list.
- search-condition
- When a trigger event occurs, the trigger SQL statement is executed if the condition specified here is true. Such a search condition is called a trigger action condition.
- If the operand trigger-action-condition is omitted, the trigger SQL statement is always executed when a specified trigger event occurs. Any of the following items cannot be specified in the trigger-action-condition operand:
- Subqueries
- Set functions
- Window function
- Repetition columns
- Embedded variables, ? parameters, SQL variables, and SQL parameters
- Columns of any of the following data types:
BLOB (however, the item can be specified in the scalar functions LENGTH and POSITION, and in function calls for a user-defined function).
BINARY with a maximum length of 32,001 bytes or greater (however, the item can be specified in the scalar functions LENGTH and POSITION, and in function calls for a user-defined function).
Abstract data type
- Structured repetition predicates
- Plug-in functions
- Value expressions producing a result that is an abstract data type (not specifiable in a value expression)
- When referencing a column of the table for which a trigger is defined in a trigger action condition, qualify the column with old-or-new-values-correlation-name (non-qualified columns cannot be specified, and columns qualified with a table name cannot be specified).
- trigger-SQL-statement
- Specifies an SQL procedure statement. For details about SQL procedure statements, see 7. Routine Control SQL.
- SQL procedure statements that are specified as a trigger SQL statement are subject to the following restrictions:
- The table name of a table for which a trigger is defined cannot be specified.
- Columns in the table for which a trigger is defined cannot be specified either without qualifying them or by qualifying them with a table name (columns qualified with an old- or new-values alias can be specified). A column qualified with an old- or new-values correlation name can be specified in the trigger SQL statement in the same locations as locations where an SQL parameter can be specified in an SQL statement. For details about locations where an SQL parameter can be specified, see 1.5 Embedded variables, indicator variables, ? parameters, SQL parameters, and SQL variables. Such a column, however, cannot be specified in the LIMIT clause.
- Table names of foreign tables cannot be specified.
- ROLLBACK, COMMIT, and PURGE TABLE statements cannot be specified. Calling a procedure specifying any of these statements using a CALL statement can produce a runtime error.
- JAVA stored procedures and the GET_JAVA_STORED_ROUTINE_SOURCE specification cannot be specified. Calling a procedure specifying a JAVA stored procedure using a CALL statement can produce a runtime error.
- SQL-compile-option::={ISOLATION data-guarantee-level [FOR UPDATE EXCLUSIVE]
|OPTIMIZE LEVEL SQL-optimization-option
[, SQL-optimization-option]...
|ADD OPTIMIZE LEVEL SQL-extension-optimizing-option
[, SQL-extension-optimizing-option]...
|SUBSTR LENGTH maximum-character-length }
In SQL-compile-option, ISOLATION, OPTIMIZE LEVEL, ADD OPTIMIZE LEVEL, and SUBSTR LENGTH can each be specified only once.
- [ISOLATION data-guarantee-level] [FOR UPDATE EXCLUSIVE]]
- Specifies an SQL data integrity guarantee level.
- data-guarantee-level
- A data integrity guarantee level specifies the point to which the integrity of the transaction data must be guaranteed. The following data integrity guarantee levels can be specified:
- 0
This option is specified when the integrity of data is not to be guaranteed. Level 0 permits the referencing of data even when the data is being updated by another user, without waiting for completion of the update process. However, if the table to be referenced is a shared table and another user is executing the LOCK statement in the lock mode, the system waits until the lock condition is released.
- 1
This option is specified when the integrity of data is to be guaranteed until the end of the retrieval process. Level 1 prevents other users from updating retrieved data until the retrieval process is completed (until HiRDB finishes viewing the page or row).
- 2
This option is specified when the integrity of retrieved data is to be guaranteed until the end of a transaction. Level 2 prevents other users from updating retrieved data until termination of the transaction.
- [FOR UPDATE EXCLUSIVE]
- Specify this operand if WITH EXCLUSIVE LOCK is always to be assumed, irrespective of the data guarantee level specified in SQL-compile-option for a cursor or query in a procedure for which the FOR UPDATE clause is specified or assumed. If level 2 is specified in data-guarantee-level, WITH EXCLUSIVE LOCK is assumed for the cursor or query in a procedure for which the FOR UPDATE clause is specified or assumed, in which case it is not necessary to specify FOR UPDATE EXCLUSIVE.
- Relationship to the client environment definition
- PDISLLVL and PDFORUPDATEEXLOCK, if specified on CREATE TRIGGER, have no effect.
- Relationship to SQL statements
- If a lock option is specified in an SQL statement in a procedure, the lock option specified in the SQL statement takes precedence over any data guarantee level specified in SQL-compile-option or the lock option assumed due to FOR UPDATE EXCLUSIVE.
- The default for this operand is Level 2.
- For data guarantee levels, see the HiRDB Version 8 UAP Development Guide.
- [OPTIMIZE LEVEL SQL-optimization-option[, SQL-optimization-option]...]
- Specifies an optimization method for determining the most efficient access path by taking the condition of the database into consideration.
- An SQL optimization option can be specified using either an identifier (character string) or a numeric value. For most cases, Hitachi recommends the use of an identifier.
- Specifying with an identifier
OPTIMIZE LEVEL "identifier"[, "identifier"]...
- Specification examples
- Applying prioritized nest-loop-join and rapid grouping processing:
OPTIMIZE LEVEL "PRIOR_NEST_JOIN", "RAPID_GROUPING"
- Applying no optimization:
OPTIMIZE LEVEL "NONE"
- Rules
- Specify one or more identifiers.
- When specifying two or more identifiers, delimit them with commas (,).
- For details about what can be specified in identifier (optimization methods), see Table 3-38 SQL optimization option specification values.
- If no optimization is to be applied, specify NONE in identifier. If an identifier other than NONE is specified at the same time, NONE is nullified.
- Identifiers can be specified in both lower case and upper case characters.
- If the same identifier is specified more than once, it is treated as if it was specified only once; however, when possible, precautions should be taken to avoid specifying a given identifier in duplicate.
- Specifying with a numeric value
OPTIMIZE LEVEL unsigned-integer[, unsigned-integer]...
- Specification examples
- Making multiple SQL objects, suppressing the use of AND multiple indexes, and forcing the use of multiple indexes
For specifying unsigned integers by delimiting them with commas:
OPTIMIZE LEVEL 4, 10, 16
For specifying the sum of unsigned integers:
OPTIMIZE LEVEL 30
- Adding a new value, 16, with the value 14 (4 + 10) already specified:
OPTIMIZE LEVEL 14, 16
- Applying no optimization:
OPTIMIZE LEVEL 0
- Rules
- When HiRDB is upgraded from a version older than Version 06-00 to a Version 06-00 or later, the total value specification in the older version also remains valid. If the optimization option does not need to be modified, the specification value for this operand need not be changed when HiRDB is upgraded to a Version 06-00 or later.
- Specify one or more unsigned integers.
- When specifying two or more unsigned integers, separate them with commas (,).
- For details about what can be specified as an unsigned integer (optimization method), see Table 3-38 SQL optimization option specification values.
- When not applying any optimization, specify 0 in unsigned-integer. However, specifying an identifier other than 0 at the same time nullifies the 0.
- If the same unsigned integer is specified more than once, it is treated as if it was specified only once; however, when possible, precautions should be taken to avoid specifying a given unsigned integer in duplicate.
- When specifying multiple optimization methods, you can specify the sum of their unsigned integers. However, care should be taken not to add the value of the same optimization method multiple times (to prevent the possibility of the resulting sum from being interpreted as a separate optimization method).
- To specify multiple optimization methods by adding their values, Hitachi recommends to separate each optimization method specification with a comma to avoid ambiguities regarding which optimization method is being specified. If a new optimization method needs to be specified after multiple optimization methods have been specified by adding their values, specify the new value by appending it, separated with a comma.
- Relationship to system definitions
- The default for the SQL optimization option is the value specified in the pd_optimize_level operand of the system definitions. For details about the pd_optimize_level operand, see the manual HiRDB Version 8 System Definition.
- When the pd_floatable_bes operand or the pd_non_floatable_bes operand is specified, specification of the Increasing the target floatable servers (back-end servers for fetching data) option or the Limiting the target floatable servers (back-end servers for fetching data) option, respectively, is invalid.
- When KEY is specified in the pd_indexlock_mode operand of the system definition (i.e., for index key value lock), specification of the Suppressing creation of update-SQL work tables option is invalid.
- Relationship to the client environment definition
- Specification of PDSQLOPTLVL has no effect on CREATE TRIGGER.
- Relationship with SQL
- If SQL optimization is specified in an SQL statement, the SQL optimization specification takes precedence over SQL optimization options. For SQL optimization specifications, see 2.24 SQL optimization specification.
- SQL optimization option specification values
- Table 3-38 shows SQL optimization option specification values. For details about optimization methods, see the HiRDB Version 8 UAP Development Guide.
Table 3-38 SQL optimization option specification values (CREATE TRIGGER)
No. | Optimization method | Specification value |
---|
Identifier | Unsigned integer |
---|
1 | Forced nest-loop-join | "FORCE_NEST_JOIN" | 4 |
2 | Making multiple SQL objects | "SELECT_APSL" | 10 |
3 | Increasing the target floatable servers (back-end servers for fetching data)#1, #2 | "FLTS_INC_DATA_BES" | 16 |
4 | Prioritized nest-loop-join | "PRIOR_NEST_JOIN" | 32 |
5 | Increasing the number of floatable server candidates#2 | "FLTS_MAX_NUMBER" | 64 |
6 | Priority of OR multiple indexes | "PRIOR_OR_INDEXES" | 128 |
7 | Group processing, ORDER BY processing, and DISTINCT set function processing at the local back-end server#2 | "SORT_DATA_BES" | 256 |
8 | Suppressing the use of AND multiple indexes | "DETER_AND_INDEXES" | 512 |
9 | Rapid grouping processing | "RAPID_GROUPING" | 1024 |
10 | Limiting the target floatable servers (back-end servers for fetching data)#1, #2 | "FLTS_ONLY_DATA_BES" | 2048 |
11 | Separating data collecting servers#1, #2 | "FLTS_SEPARATE_COLLECT_SVR" | 2064 |
12 | Suppressing index use (forced table scan) | "FORCE_TABLE_SCAN" | 4096 |
13 | Forcing use of multiple indexes | "FORCE_PLURAL_INDEXES" | 32768 |
14 | Suppressing creation of update-SQL work tables | "DETER_WORK_TABLE_FOR_UPDATE" | 131072 |
15 | Derivation of rapid search conditions | "DERIVATIVE_COND" | 262144 |
16 | Applying key conditions including scalar operations | "APPLY_ENHANCED_KEY_COND" | 524288 |
17 | Facility for batch acquisition from functions provided by plug-ins | "PICKUP_MULTIPLE_ROWS_PLUGIN" | 1048576 |
- #1: If both Increasing the target floatable servers (back-end servers for fetching data) and Limiting the target floatable servers (back-end servers for fetching data) are specified together, the respective optimization method does not take effect; instead, the specification operates as a separating data collecting server.
- #2: When specified on a HiRDB/Single Server, this option has no effect.
- [ADD OPTIMIZE LEVEL SQL-extension-optimizing-option[, SQL-extension-optimizing-option]...]
- Specifies an optimization method for determining the most efficient access path by taking the condition of the database into consideration.
- An SQL extension optimizing option can be specified using either an identifier (character string) or a numeric value.
- Specifying with an identifier
ADD OPTIMIZE LEVEL "identifier"[, "identifier"]...
- Specification examples
- Applying Optimizing mode 2 based on cost and Hash join, subquery hash execution:
ADD OPTIMIZE LEVEL "COST_BASE_2", "APPLY_HASH_JOIN"
- Applying no optimization:
ADD OPTIMIZE LEVEL "NONE"
- Rules
- Specify one or more identifiers.
- When specifying two or more identifiers, delimit them with commas (,).
- For details about what can be specified in identifier (optimization methods), see Table 3-39 SQL extension optimizing option specification values.
- If no optimization is to be applied, specify NONE in identifier.
- Identifiers can be specified in both lower case and upper case characters.
- If the same identifier is specified more than once, it is treated as if it was specified only once; however, when possible, precautions should be taken to avoid specifying a given identifier in duplicate.
- Specifying with a numeric value
ADD OPTIMIZE LEVEL unsigned-integer[, unsigned-integer]...
- Specification examples
- Applying Optimizing mode 2 based on cost and Hash join, subquery hash execution:
ADD OPTIMIZE LEVEL 1, 2
- Applying no optimization:
ADD OPTIMIZE LEVEL 0
- Rules
- Specify one or more unsigned integers.
- When specifying two or more unsigned integers, separate them with commas (,).
- For details about what can be specified as an unsigned integer (optimization method), see Table 3-39 SQL extension optimizing option specification values.
- When not applying any optimization, specify 0 in unsigned-integer.
- If the same unsigned integer is specified more than once, it is treated as if it was specified only once; however, when possible, precautions should be taken to avoid specifying a given unsigned integer in duplicate.
- Relationship to system definitions
- The default for the SQL extension optimizing option is the value specified in the pd_additional_optimize_level operand of system definitions. For details about the pd_additional_optimize_level operand, see the manual HiRDB Version 8 System Definition.
- Relationship to the client environment definition
- Specification of PDADDITIONALOPTLVL has no effect on CREATE TRIGGER.
- Relationship with SQL
- If SQL optimization is specified in an SQL statement, the SQL optimization specification takes precedence over SQL optimization options. For SQL optimization specifications, see 2.24 SQL optimization specification.
- SQL extension optimizing option specification values
- Table 3-39 shows SQL extension optimizing option specification values. For details about optimization methods, see the HiRDB Version 8 UAP Development Guide.
Table 3-39 SQL extension optimizing option specification values (CREATE TRIGGER)
No. | Optimization method | Specification value |
---|
Identifier | Unsigned integer |
---|
1 | Application of optimizing mode 2 based on cost | "COST_BASE_2" | 1 |
2 | Hash join, subquery hash execution | "APPLY_HASH_JOIN" | 2 |
3 | Suppressing the foreign server execution of SQL statements including joins | "DETER_JOIN_SQL" | 67108864 |
4 | Forced foreign server execution of SQL statements including direct products | "FORCE_CROSS_JOIN_SQL" | 134217728 |
5 | Suppressing the derivation of rapid search conditions that are unconditionally generated and can be executed on a foreign server | "DETER_FSVR_DERIVATIVE_COND" | 1073741824 |
- Note 1
- Items 2-5 take effect when Application of optimizing mode 2 based on cost is specified.
- Note 2
- Optimization items 3-5 are effective for retrieving foreign tables; for other purposes, these items have no effect.
- [SUBSTR LENGTH maximum-character-length]
- Specifies the maximum number of bytes for representing a single character.
- The value specified for the maximum character length must be in the range from 3 to6.
- This operand is valid only when utf-8 is specified for the character code type in the pdntenv command (pdsetup command in the case of the UNIX version); it affects the length of the result of the SUBSTR scalar function. For details about SUBSTR, see 2.16.1(21) SUBSTR.
- Relationships to system definition
- When SUBSTR LENGTH is omitted, the value specified in the pd_substr_length operand in the system definition is assumed. For details about the pd_substr_length operand, see the manual HiRDB Version 8 System Definition.
- Relationship to client environmental definition
- The specification of PDSUBSTREN has no applicability to CREATE TRIGGER. For details about PDSUBSTRLEN, see the manual HiRDB Version 8 UAP Development Guide.
- Relationship to the character code type specified in the pdntenv or pdsetup command
- This operand is valid only when utf-8 is specified for the character code type.
- For all other character code types, only a syntax check is performed and the specification is ignored.
When defining a trigger, if there is a function, a procedure, or an SQL object for which a trigger is in effect and that uses the table for which the trigger is being defined, specify this option when nullifying the SQL object:
- If trigger-event is INSERT, a function, a procedure, or a trigger that inserts rows into the table for which the trigger is being defined
- If trigger-event is UPDATE, a function, a procedure, or a trigger that updates rows in the table for which the trigger is being defined
- If trigger-event is DELETE, a function, a procedure, or a trigger that deletes rows from the table for which the trigger is being defined
All functions and procedures that created an SQL object in HiRDB of Version 07-00 or earlier are nullified even if they do not meet the above conditions.
The CREATE TRIGGER example uses an inventory history table (HSTOCK), a Glasgow inventory table, and a Edinburgh inventory table in addition to an inventory table (STOCK). The Glasgow and Edinburgh inventory tables have the same organization as the inventory table. The inventory history table is organized as follows:
![[Figure]](figure/zu3s0120.gif)
- After a row is inserted into the inventory table (STOCK), define a trigger (INSERTTRIG1) that inserts information on the inserted row into the inventory history table (HSTOCK):
CREATE TRIGGER INSERTTRIG1
AFTER INSERT ON STOCK
REFERENCING NEW ROW X1
FOR EACH ROW
INSERT INTO HSTOCK
VALUES(X1.PCODE,NULL,X1.SQTY,
CURRENT_DATE,CURRENT_TIME)
- After the quantity (SQTY) is updated in the inventory table (STOCK), define a trigger (INSERTTRIG2) that inserts pre- and post-update values into the inventory history table (HSTOCK):
CREATE TRIGGER INSERTTRIG2
AFTER UPDATE OF SQTY ON STOCK
REFERENCING NEW ROW X1 OLD ROW Y1
FOR EACH ROW
INSERT INTO HSTOCK
VALUES(Y1.PCODE,Y1.SQTY,X1.SQTY,
CURRENT_DATE,CURRENT_TIME)
- After a row is deleted from the inventory table (STOCK), define a trigger (INSERTTRIG3) that inserts information on the deleted row into the inventory history table (HSTOCK):
CREATE TRIGGER INSERTTRIG3
AFTER DELETE ON STOCK
REFERENCING OLD ROW Y1
FOR EACH ROW
INSERT INTO HSTOCK
VALUES(Y1.PCODE,Y1.SQTY,NULL,
CURRENT_DATE,CURRENT_TIME)
- Use a routine control SQL (a compound statement) in trigger-action. After the inventory table (STOCK) is updated, define a trigger (UPDATELOCAL) that puts the update into effect in the Glasgow and Edinburgh inventory tables. If a compound statement is not used, two triggers that are set off upon the updating of the inventory table must be defined.
CREATE TRIGGER UPDATELOCAL
AFTER UPDATE OF SQTY ON STOCK
REFERENCING NEW ROW X1 OLD ROW Y1
BEGIN
UPDATE glasgow-inventory-table SET SQTY=X1.SQTY
WHERE PCODE=Y1.PCODE;
UPDATE edinburgh-inventory-table SET SQTY=X1.SQTY
WHERE PCODE=Y1.PCODE;
END
- Use a routine control SQL (an assignment statement) in trigger-action. Define a trigger (SETPRICE) that assigns the following amount to the row to be inserted into the inventory table (STOCK): an amount equal to the unit price (PRICE) to be inserted plus 50.00 dollars if the product code (PCODE) is 101M, 201M, or an amount equal to the unit price to be inserted multiplied by 1.2 if the (PCODE) is not 101M, 201M, or 301M:
CREATE TRIGGER SETPRICE
BEFORE INSERT ON STOCK
REFERENCING NEW ROW AS X1
FOR EACH ROW
SET X1.PRICE=CASE X1.PCODE
WHEN '101M' THEN X1.PRICE + 50
WHEN '201M' THEN X1.PRICE + 50
WHEN '301M' THEN X1.PRICE + 50
ELSE X1.PRICE * 1.2
END
- Use an SQL diagnostic statement (SIGNAL statement) in trigger-action. Define a trigger (SIGNALTRIG) that suppresses the deletion of rows from the inventory table before rows in the inventory table (STOCK) are deleted:
CREATE TRIGGER SIGNALTRIG
BEFORE DELETE ON STOCK
SIGNAL SQLSTATE '99001'