且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

MYSQL中的触发器(更新后)

更新时间:2023-02-04 23:40:55

您需要以编程方式检查NEW.col1< => OLD.col1

Hello friends,
I am creating Trigger in MYSQL and I got some error there its work well in MS-SQL.
Please tell me my error.

\\code

delimiter //
Create Trigger trgAfterUpdate AFTER UPDATE ON incident FOR EACH ROW
begin
 declare item_id varchar(20);
 declare subject varchar(100) ;
 declare work_flow varchar(20) ;
 declare team_member varchar(20) ;
 declare types_of_graphics varchar(20) ;
 declare department varchar(30) ;
 declare graphics_type varchar(30) ;
 declare item_mode varchar(30) ;
 declare in_time varchar(20) ;
 declare out_time datetime ;
 declare status varchar(20) ;
 declare priority varchar(20) ;
 declare description longtext ;
 declare Audit_Action varchar(100) ;
 declare Audit_Timestamp datetime ;
 set item_id = new.item_id;
 set subject = new.subject;
 set work_flow = new.work_flow;
 set team_member = new.team_member;
 set types_of_graphics = new.types_of_graphics;
 set graphics_type = new.graphics_type;
 set item_mode = new.item_mode;
 set out_time = new.out_time;
 set status = new.status;
 set priority = new.priority;
 set description = new.description;
 set audit_action='Updated Record -- After Update Trigger.';



IF UPDATE(work_flow)
        THEN  @audit_action = 'Updated work_flow -- After Update Trigger.';
    ELSEIF update(team_member)
        Then set @audit_action='Updated team_member -- After Update Trigger.';
            Else update(status)
         set @audit_action='Updated status -- After Update Trigger.';

 End IF

  INSERT INTO incident_audit (item_id, subject, work_flow, team_member, types_of_graphics, department, graphics_type, item_mode, in_time, out_time, status, priority, description, Audit_Action, Audit_Timestamp) VALUES (item_id, subject, work_flow, team_member, types_of_graphics, department, graphics_type, item_mode, in_time, out_time, status, priority, description, Audit_Action, sysdate());
 END

You will need to programmatically check for NEW.col1 <=> OLD.col1