Operations Guide

10.0
- 10.5 HotFix 1
- 10.5
- 10.2 HotFix 1
- 10.2
- 10.1 HotFix 1
- 10.1
- 10.0 HotFix 1

Back Next

MySQL Triggers

Due to several limitations of triggers in MySQL, trigger creation for USTs residing on MySQL databases may involve some manual intervention if triggers are already being used on the UST.

This section describes how IIR implements triggers in a MySQL environment and the manual steps which must be followed.

Trigger Verification

When the Table Loader accesses a UST referenced by an IIR System which needs to be synchronized, it usually creates Triggers on that UST. In the case of MySQL, this cannot be done if there are already triggers on the table. You need to drop the triggers, create the new triggers, and then merge the old and new triggers manually.


loadit> ErrorLog: [124123029   2] sql.c        1078 rc  30
loadit> ErrorLog: [124123029   2] prepare_sql: Error adding 3 trigger(s)

loadit> ErrorLog: [124123029   2] trigger type 'I' on table 'SSA12.BONUS'
not present. Generated code is in 'd:\work\IDS_DB_TESTX261_TESTX261_BONUS_I.sql'
loadit> ErrorLog: [124123029   2] trigger type 'U' on table 'SSA12.BONUS'
not present. Generated code is in 'd:\work\IDS_DB_TESTX261_TESTX261_BONUS_U.sql'
loadit> ErrorLog: [124123029   2] trigger type 'D' on table 'SSA12.BONUS'
not present. Generated code is in 'd:\work\IDS_DB_TESTX261_TESTX261_BONUS_D.sql'

Here is a sample of a generated Trigger:


CREATE TRIGGER IDS00001 AFTER UPDATE ON ssa.EMP
FOR EACH ROW BEGIN
    IF OLD.EMPNO != NEW.EMPNO OR OLD.ENAME != NEW.ENAME OR
            OLD.JOB != NEW.JOB OR OLD.DEPTNO != NEW.DEPTNO  THEN
        CALL ssa.IDS_UPD_SYNC ('sysname', '1', 0, 'ssa.EMP', 3,
            'IDS_01_IDT76', 'IDS_EmpNum',OLD.EMPNO,
	    '','','','','','','','','','','','','');
        CALL ssa.IDS_UPD_SYNC ('sysname', '2', 0, 'ssa.EMP', 3,
            'IDS_01_IDT76', 'ssa.EMP.EMPNO',NEW.EMPNO,
                '','','','','','','','','','','','','');
    END IF;
END

Rename Saved Search

Table of Contents

Operations Guide

Operations Guide

MySQL Triggers

MySQL Triggers

Trigger Verification