MySQL Trigger for tracking updates or deletes

Client Joined: Oct 07, 2003 Posts: 735 Location: Ohio

Is this doable?

I am trying to capture the admin who may change grades.

Code:




CREATE TRIGGER irat_grade_after_update AFTER UPDATE ON atlas_tl_session_grades


FOR EACH ROW BEGIN


INSERT INTO atlas_tl_audit_grades SET action='update', Session_ID=OLD.Session_ID, old_raw_grade=OLD.IRAT_RAW, raw_grade=NEW.raw_grade, UID=OLD.UID, changed=NOW(), changed_by = SUBSTRING_INDEX(USER(),'@',1);


END;

From what I have read this

Code:

SUBSTRING_INDEX(USER(),'@',1);

will capture .. [ Only registered users can see links on this board! Get registered or login! ]

I then get everything before the @ and I'm good to go.

Posted: Thu May 15, 2008 7:27 am

Or maybe this.

Code:

CREATE TRIGGER irat_grade_after_update AFTER UPDATE ON atlas_tl_session_grades


SELECT @admin_user := SUBSTRING_INDEX(USER(),'@',1);


FOR EACH ROW BEGIN


INSERT INTO atlas_tl_audit_grades;


SET action='update', Session_ID=OLD.Session_ID, old_raw_grade=OLD.IRAT_RAW, raw_grade=NEW.raw_grade, UID=OLD.UID, changed=NOW(), changed_by = @admin_user;


END;