Creation Trigger with variables

[morakevi]

Hello,

I try to create this trigger:

CREATE TRIGGER TW_OPERATOR_DELETE
    AFTER DELETE ON secadm.operateur 
    REFERENCING OLD AS oldOperator
    FOR EACH ROW
    BEGIN ATOMIC
        DECLARE old_uref char(8);
        DECLARE old_created timestamp;
        DECLARE old_modified timestamp;
        DECLARE old_version smallint;
        DECLARE old_author varchar(8);
        DECLARE old_status smallint;
        DECLARE old_teleworking_type smallint;
        DECLARE old_note_id bigint;
 
        SELECT operateur_uref, created, modified, version, author, status, 
        teleworking_type, note_id INTO old_uref, old_created, old_modified, 
        old_version, old_author, old_status, old_teleworking_type, old_note_id 
        FROM material.operator_extended WHERE operateur_uref = oldOperator.uref;
 
        INSERT INTO material.operator_extended_versioned (operateur_uref,
        created, modified, deleted, version, author, status, teleworking_type,
        note_id) VALUES (old_uref, old_created, old_modified, current timestamp, 
        old_version, old_author, old_status, old_teleworking_type, old_note_id);
 
        DELETE FROM material.operator_extended WHERE operateur_uref = old_uref;
    END

But I have this error => An unexpected token "old_uref" was found following "g_type, note_id INTO". Expected tokens may include: "<space>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.7.85

And I don't found why.

[Anas Mosaad]

I can see that you are trying to implement versioning to your operators table. If you are using DB2 10.1 you can use system period tables to implement this seamlessly by DB2 system.

If you still need to do this your own, you can call a procedure that does whatever you want.