增加、删除、更新触发器

第一步：新建触发器表

SET FOREIGN_KEY_CHECKS=0;DROP TABLE IF EXISTS `dataex_trigger_record`;CREATE TABLE `dataex_trigger_record` (  `ROWGUID` varchar(50) NOT NULL,  `TABLENAME` varchar(50) DEFAULT NULL,  `COLUMNNAME` varchar(50) DEFAULT NULL,  `COLUMNVALUE` varchar(100) NOT NULL,  `INSERTDATE` date DEFAULT NULL,  `OPERATETYPE` varchar(10) DEFAULT NULL,  `SYNC_DATE` date DEFAULT NULL,  `SYNC_SIGN` varchar(10) DEFAULT NULL,  `SYNC_ERROR_DESC` varchar(1000) DEFAULT NULL,  PRIMARY KEY (`ROWGUID`)) ;

第二步：新建触发器

– sqlserver

CREATE TRIGGER E_DATA_FROM_A ON data_from FOR INSERT ASBEGIN    INSERT INTO DATAEX_TRIGGER_RECORD (        ROWGUID,        TABLENAME,        COLUMNNAME,        Columnvalue,        INSERTDATE,        Operatetype    )(        SELECT            newid (),            'data_from',            'id',            id,            getdate (),            'I'        FROM            inserted    );END;

CREATE TRIGGER E_DATA_FROM_U ON data_from FOR UPDATE ASBEGININSERT INTO DATAEX_TRIGGER_RECORD (    ROWGUID,    TABLENAME,    COLUMNNAME,    Columnvalue,    INSERTDATE,    Operatetype)(    SELECT        newid (),        'data_from',        'id',        id,        getdate (),        'U'    FROM        inserted);END;

CREATE TRIGGER E_DATA_FROM_D ON data_from FOR DELETE ASBEGININSERT INTO DATAEX_TRIGGER_RECORD (    ROWGUID,    TABLENAME,    COLUMNNAME,    Columnvalue,    INSERTDATE,    Operatetype)(    SELECT        newid (),        'data_from',        'id',        id,        getdate (),        'D'    FROM        deleted);END;

– mysql/oracle

CREATE TRIGGER E_DATA_FROM_A AFTER INSERT ON data_from FOR EACH ROWBEGIN    INSERT INTO DATAEX_TRIGGER_RECORD (        ROWGUID,        TABLENAME,        COLUMNNAME,        Columnvalue,        INSERTDATE,        Operatetype    )VALUES    (        UUID(),        'data_from',        'id',        new.id,        SYSDATE(),        'I'    );END;

CREATE TRIGGER E_DATA_FROM_U AFTER UPDATE ON data_from FOR EACH ROWBEGININSERT INTO DATAEX_TRIGGER_RECORD (    ROWGUID,    TABLENAME,    COLUMNNAME,    Columnvalue,    INSERTDATE,    Operatetype)VALUES    (        UUID(),        'data_from',        'id',        new.id,        SYSDATE(),        'U'    );END;

CREATE TRIGGER E_DATA_FROM_D AFTER DELETE ON data_from FOR EACH ROWBEGININSERT INTO DATAEX_TRIGGER_RECORD (    ROWGUID,    TABLENAME,    COLUMNNAME,    Columnvalue,    INSERTDATE,    Operatetype)VALUES    (        UUID(),        'data_from',        'id',        old.id,        SYSDATE(),        'D'    );END;

–mysql针对具体的表

CREATE TRIGGER E_T_RK_ INFO_161W_A AFTER INSERT ON t_rk_ info_161w FOR EACH ROWBEGIN    INSERT INTO DATAEX_TRIGGER_RECORD (        ROWGUID,        TABLENAME,        COLUMNNAME,        Columnvalue,        INSERTDATE,        Operatetype    )VALUES    (        UUID(),        't_rk_ info_161w',        'ROW_ID',        new.ROW_ID,        SYSDATE(),        'I'    );END;

CREATE TRIGGER E_T_RK_ INFO_161W_U AFTER UPDATE ON t_rk_ info_161w FOR EACH ROWBEGININSERT INTO DATAEX_TRIGGER_RECORD (    ROWGUID,    TABLENAME,    COLUMNNAME,    Columnvalue,    INSERTDATE,    Operatetype)VALUES    (        UUID(),        't_rk_ info_161w',        'ROW_ID',        new.ROW_ID,        SYSDATE(),        'U'    );END;

CREATE TRIGGER E_T_RK_ INFO_161W_D AFTER DELETE ON t_rk_ info_161w FOR EACH ROWBEGININSERT INTO DATAEX_TRIGGER_RECORD (    ROWGUID,    TABLENAME,    COLUMNNAME,    Columnvalue,    INSERTDATE,    Operatetype)VALUES    (        UUID(),        't_rk_ info_161w',        'ROW_ID ',        old.ROW_ID,        SYSDATE(),        'D'    );END;