Trigger Conversion Rules

Trigger Conversion Rules

RULE-12002

Version Scope: Less than the Altibase version 6.3.1.0.0

Type

TODO

Description

'INSTEAD OF' should be manually converted

Original SQL Text

CREATE OR REPLACE TRIGGER log_attendance
INSTEAD OF INSERT ON attendance_view FOR EACH ROW
BEGIN
IF :NEW.cnt < 2 THEN
INSERT INTO daily_log VALUES(:NEW.id, CURRENT_TIMESTAMP);
END IF;
END;

Processed SQL Text

CREATE OR REPLACE TRIGGER log_attendance
INSTEAD OF /* [TODO] RULE-12002 : 'INSTEAD OF' must be converted manually */ INSERT ON attendance_view FOR EACH ROW
BEGIN
IF :NEW.cnt < 2 THEN
INSERT INTO daily_log VALUES(:NEW.id, CURRENT_TIMESTAMP);
END IF;
END;

RULE-12003

Type

TODO

Description

Triggers supporting multiple events must be converted manually.

Original SQL Text

CREATE OR REPLACE TRIGGER trig1
BEFORE INSERT OR DELETE ON t1
BEGIN
NULL;
END;

Processed SQL Text

CREATE OR REPLACE TRIGGER trig1
BEFORE INSERT OR DELETE ON t1 /* [TODO] RULE-12003 : Triggers supporting multiple events must be converted manually */
BEGIN
NULL;
END;

RULE-12004

This rule is about the DECLARE clause used within a PSM block, and is applied differently depending on the Altibase server version.

Type

TODO

Less than the Altibase version 6.3.1.0.0

Description

AS or IS should be used regardless of that DECLARE exists or not in the PSM block.

Original SQL Text

CREATE OR REPLACE TRIGGER trig1
BEFORE INSERT ON t1
BEGIN
NULL;
END;

Processed SQL Text

CREATE OR REPLACE TRIGGER trig1
BEFORE INSERT ON t1
BEGIN /* [TODO] RULE-12004 : 'AS' or 'IS' should be used regardless of that DECLARE exists or not in the PSM block. */
NULL;
END;

Altibase 6.3.1.0.0-6.5.1.3.7 or below

Description

The DECLARE preceding the PSM body should be replaced with AS or IS.

Original SQL Text

CREATE OR REPLACE TRIGGER trig1
BEFORE INSERT ON t1
DECLARE
v1 NUMBER := 1;
BEGIN
NULL;
END;

Processed SQL Text

CREATE OR REPLACE TRIGGER trig1
BEFORE INSERT ON t1
DECLARE /* [TODO] RULE-12004 : 'AS' or 'IS' must replace 'DECLARE' that starts the declarative part of the block */
v1 NUMBER := 1;
BEGIN
NULL;
END;

RULE-12005

Type

TODO

Description

Non-DML triggers must be converted manually.

Original SQL Text

CREATE OR REPLACE TRIGGER trig1
BEFORE CREATE ON DATABASE
BEGIN
NULL;
END;

Processed SQL Text

CREATE OR REPLACE TRIGGER trig1
BEFORE CREATE ON DATABASE /* [TODO] RULE-12005 : Non DML trigger must be converted manually */
BEGIN
NULL;
END;

RULE-12007

Type

TODO

Description

Nested tables must be converted manually.

Original SQL Text

CREATE OR REPLACE TRIGGER trig1
INSTEAD OF DELETE ON NESTED TABLE t1 OF v1
BEGIN
NULL;
END;

Processed SQL Text

CREATE OR REPLACE TRIGGER trig1
INSTEAD OF DELETE ON NESTED TABLE t1 OF v1 /* [TODO] RULE-12007 : Nested table must be converted manually */
BEGIN
NULL;
END;

RULE-12008

Type

TODO

Description

The CALL routine clause must be converted manually.

Original SQL Text

CREATE OR REPLACE TRIGGER trig1
AFTER DELETE ON t1
CALL testproc1(a1, a2);

Processed SQL Text

CREATE OR REPLACE TRIGGER trig1
AFTER DELETE ON t1
CALL testproc1(a1, a2) /* [TODO] RULE-12008 : CALL routine clause must be converted manually */;

RULE-12009

Type

TODO

Description

The parent row of a nested table cannot be specified.

Original SQL Text

CREATE OR REPLACE TRIGGER trig1
INSTEAD OF DELETE ON NESTED TABLE t1 OF v1
REFERENCING PARENT AS parent FOR EACH ROW
BEGIN
NULL;
END;

Processed SQL Text

CREATE OR REPLACE TRIGGER trig1
INSTEAD OF DELETE ON NESTED TABLE t1 OF v1
REFERENCING PARENT AS parent /* [TODO] RULE-12009 : Parent value of the current row cannot be specified */ FOR EACH ROW
BEGIN
NULL;
END;

RULE-12010

Type

TODO

Description

The trigger ordering clause should be converted manually.

Original SQL Text

CREATE OR REPLACE TRIGGER trig1
AFTER DELETE ON t1
FOLLOWS trig2
BEGIN
NULL;
END;

Processed SQL Text

CREATE OR REPLACE TRIGGER trig1
AFTER DELETE ON t1
FOLLOWS trig2 /* [TODO] RULE-12010 : Trigger ordering clause must be converted manually */
BEGIN
NULL;
END;

RULE-12011

Type

CONVERTED

Description

The ommitted correlation name has been added in the REFERENCING clause.

Original SQL Text

CREATE OR REPLACE TRIGGER trig1
AFTER INSERT ON t1 FOR EACH ROW
BEGIN
:new.c1 := SYSDATE;
END;

Processed SQL Text

CREATE OR REPLACE TRIGGER trig1
AFTER INSERT ON t1
REFERENCING NEW AS new FOR EACH ROW
DECLARE
BEGIN
:new.c1 := SYSDATE;
END;

RULE-12012

Type

CONVERTED

Description

A suffix has been added to the local identifier corresponding to the reserved words of Altibase.

Original SQL Text

CREATE OR REPLACE TRIGGER trig1
AFTER UPDATE ON t1
REFERENCING NEW AS new OLD AS old FOR EACH ROW
BEGIN
NULL;
END;

Processed SQL Text

CREATE OR REPLACE TRIGGER trig1
AFTER UPDATE ON t1
REFERENCING NEW AS new_POC OLD AS old_POC FOR EACH ROW
BEGIN
NULL;
END;

RULE-12013

Type

REMOVED

Description

The trigger edition clause has been removed.

Original SQL Text

CREATE OR REPLACE TRIGGER trig1
AFTER DELETE ON t1
CROSSEDITION
BEGIN
NULL;
END;

Processed SQL Text

CREATE OR REPLACE TRIGGER trig1
AFTER DELETE ON t1
/* CROSSEDITION */ /* [REMOVED] RULE-12013 : Trigger edition clause is removed */
BEGIN
NULL;
END;

RULE-12014

Type

REMOVED

Description

The ENABLE is removed.

Original SQL Text

CREATE OR REPLACE TRIGGER trig1
AFTER INSERT ON t1
ENABLE
BEGIN
NULL;
END;

Processed SQL Text

CREATE OR REPLACE TRIGGER trig1
AFTER INSERT ON t1
/* ENABLE */ /* [REMOVED] RULE-12014 : ENABLE is removed */
BEGIN
NULL;
END;

RULE-12015

Type

TODO

Description

The DISABLE should be converted manually.

Original SQL Text

CREATE OR REPLACE TRIGGER trig1
AFTER DELETE ON t1
DISABLE
BEGIN
NULL;
END;

Processed SQL Text

CREATE OR REPLACE TRIGGER trig1
AFTER DELETE ON t1
DISABLE /* [TODO] RULE-12015 : DISABLE must be converted manually */
BEGIN
NULL;
END;

RULE-12016

Type

CONVERTED

Description

The colon preceding the alias referring to the rows defined in the REFERENCING clause has been eliminated.

Original SQL Text

CREATE OR REPLACE TRIGGER trig1
BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(:new.c1);
END;

Processed SQL Text

CREATE OR REPLACE TRIGGER trig1
BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(new.c1);
END;

RULE-12017

Type

REMOVED

Description

The trigger label name at the end of PL/SQL block has been removed in the CREATE TRIGGER statement.

Original SQL Text

CREATE OR REPLACE TRIGGER trig1
AFTER INSERT ON t1
BEGIN
NULL;
END trig1;

Processed SQL Text

CREATE OR REPLACE TRIGGER trig1
AFTER INSERT ON t1
BEGIN
NULL;
END /* trig1 */ /* [REMOVED] RULE-12017 : The trigger label name at the end of body has been removed */;