SQL Server和MySQL触发器

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;