Click here to Skip to main content
15,905,563 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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
SQL
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.';


SQL
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
Posted
Updated 10-Apr-13 0:13am
v3
Comments
Neetesh Agarwal 30-Mar-13 5:07am    
I got Problem in Update Block......... IF UPDATE(work_flow)
What is the error or exception ?
CHill60 10-Apr-13 6:34am    
What is the error?
Neetesh Agarwal 10-Apr-13 7:07am    
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE(work_flow)
THEN @audit_action = 'Updated work_flow -- After Update' at line 30
Neetesh Agarwal 10-Apr-13 7:07am    
Which function I can use here Instead of Update ?

1 solution

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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900