增加、删除、更新触发器

第一步：新建触发器表

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 AS
BEGIN
    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 AS
BEGIN
INSERT 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 AS
BEGIN
INSERT 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 ROW
BEGIN
    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 ROW
BEGIN
INSERT 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 ROW
BEGIN

INSERT 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_BASEINFO_161W_A AFTER INSERT ON t_rk_baseinfo_161w FOR EACH ROW
BEGIN
    INSERT INTO DATAEX_TRIGGER_RECORD (
        ROWGUID,
        TABLENAME,
        COLUMNNAME,
        Columnvalue,
        INSERTDATE,
        Operatetype
    )
VALUES
    (
        UUID(),
        't_rk_baseinfo_161w',
        'ROW_ID',
        new.ROW_ID,
        SYSDATE(),
        'I'
    );


END;

CREATE TRIGGER E_T_RK_BASEINFO_161W_U AFTER UPDATE ON t_rk_baseinfo_161w FOR EACH ROW
BEGIN
INSERT INTO DATAEX_TRIGGER_RECORD (
    ROWGUID,
    TABLENAME,
    COLUMNNAME,
    Columnvalue,
    INSERTDATE,
    Operatetype
)
VALUES
    (
        UUID(),
        't_rk_baseinfo_161w',
        'ROW_ID',
        new.ROW_ID,
        SYSDATE(),
        'U'
    );


END;

CREATE TRIGGER E_T_RK_BASEINFO_161W_D AFTER DELETE ON t_rk_baseinfo_161w FOR EACH ROW
BEGIN

INSERT INTO DATAEX_TRIGGER_RECORD (
    ROWGUID,
    TABLENAME,
    COLUMNNAME,
    Columnvalue,
    INSERTDATE,
    Operatetype
)
VALUES
    (
        UUID(),
        't_rk_baseinfo_161w',
        'ROW_ID ',
        old.ROW_ID,
        SYSDATE(),
        'D'
    );


END;