SQL Server
DROP TRIGGER wy_dy_insert_trigger;
DROP TRIGGER wy_dy_delete_trigger;
DROP TRIGGER wy_dy_update_trigger;
CREATE TRIGGER wy_dy_insert_trigger ON WY_DY AFTER INSERT AS
BEGIN
BEGIN TRY
DECLARE @Id bigint
DECLARE cursor_dy CURSOR forward_only static read_only FOR SELECT it.DYID FROM INSERTED it
OPEN cursor_dy
FETCH NEXT FROM cursor_dy INTO @Id
WHILE @@fetch_status=0
BEGIN
INSERT INTO wy_dy_operation_log(id, type, business, description, create_time) VALUES(NEWID(), 'add','b1', @Id, GETDATE());
fetch next from cursor_dy into @Id
END
CLOSE cursor_dy
DEALLOCATE cursor_dy
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH
END;
CREATE TRIGGER wy_dy_delete_trigger ON WY_DY AFTER DELETE AS
BEGIN
BEGIN TRY
DECLARE @Id bigint
DECLARE cursor_dy CURSOR forward_only static read_only FOR SELECT dt.DYID FROM DELETED dt
OPEN cursor_dy
FETCH NEXT FROM cursor_dy INTO @Id
WHILE @@fetch_status=0
BEGIN
INSERT INTO wy_dy_operation_log(id, type,business, description, create_time) VALUES(NEWID(), 'delete','b1', @Id, GETDATE())
fetch next from cursor_dy into @Id
END
CLOSE cursor_dy
DEALLOCATE cursor_dy
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH
END;
CREATE TRIGGER wy_dy_update_trigger ON WY_DY AFTER UPDATE AS
BEGIN
BEGIN TRY
DECLARE @Id bigint
DECLARE @old_bianhao varchar(200)
DECLARE @old_louceng varchar(200)
DECLARE @old_fanghao varchar(200)
DECLARE @new_bianhao varchar(200)
DECLARE @new_louceng varchar(200)
DECLARE @new_fanghao varchar(200)
DECLARE @Cols varchar(500)
DECLARE cursor_dy CURSOR forward_only static read_only FOR
SELECT it.DYID, it.编号,it.楼层,it.房号,dt.编号,dt.楼层,dt.房号 FROM INSERTED it,DELETED dt WHERE it.DYID=dt.DYID
OPEN cursor_dy
FETCH NEXT FROM cursor_dy INTO @Id, @new_bianhao, @new_louceng, @new_fanghao, @old_bianhao, @old_louceng, @old_fanghao
WHILE @@fetch_status=0
BEGIN
SET @Cols = '';
IF @old_bianhao <> @new_bianhao
BEGIN
SET @Cols=@Cols+'编号'+','
END
IF @old_louceng <> @new_louceng
BEGIN
SET @Cols=@Cols+'楼层'+','
END
IF @old_fanghao <> @new_fanghao
BEGIN
SET @Cols=@Cols+'房号'+','
END
IF datalength(@Cols)>0
BEGIN
SET @Cols = CAST(@Id AS VARCHAR(100))+'|'+ @Cols
INSERT INTO wy_dy_operation_log(id, type, business,description, create_time) VALUES(NEWID(), 'update','b1', @Cols, GETDATE());
END
fetch next from cursor_dy into @Id,@new_bianhao, @new_louceng, @new_fanghao, @old_bianhao, @old_louceng, @old_fanghao
END
CLOSE cursor_dy
DEALLOCATE cursor_dy
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH
END;
MYSQL
DROP TRIGGER wy_dy_insert_trigger;
CREATE TRIGGER wy_dy_insert_trigger AFTER INSERT ON hr_staff_entry FOR EACH ROW
INSERT INTO wy_dy_operation_log(id, type, description, create_time) VALUES(replace(uuid(),'-',''), 'add', new.fd_id, NOW());
DROP TRIGGER wy_dy_delete_trigger;
CREATE TRIGGER wy_dy_delete_trigger AFTER DELETE ON hr_staff_entry FOR EACH ROW
INSERT INTO wy_dy_operation_log(id, type, description, create_time) VALUES(replace(uuid(),'-',''), 'delete', old.fd_id, NOW());
DROP TRIGGER wy_dy_update_trigger;
CREATE TRIGGER wy_dy_update_trigger AFTER UPDATE ON hr_staff_entry FOR EACH ROW
BEGIN
SET @Cols = 'Updated Columns: ';
IF old.fd_name <> new.fd_name THEN
SET @Cols = CONCAT(@Cols, 'fd_name, ');
END IF;
IF old.fd_name_used_before <> new.fd_name_used_before THEN
SET @Cols = CONCAT(@Cols, 'fd_name_used_before, ');
END IF;
IF old.fd_sex <> new.fd_sex THEN
SET @Cols = CONCAT(@Cols, 'fd_sex, ');
END IF;
INSERT INTO wy_dy_operation_log(id, type, description, create_time) VALUES(replace(uuid(),'-',''), 'update', CONCAT(new.fd_id,'||',@Cols), NOW());
END;