November 29th, 2013, 03:34 AM
Why doesn't my trigger work?
I am trying to define a trigger in which if inserted email is null, change the Email to(a-Mail address blocked: See forum rules), where xxx
is the SNb.
This is the table:
Students (SNb, SName, amail, Gender, ClassYear, GPA)
CREATE TRIGGER amail_replace_students AFTER INSERT
DECLARE host varchar(10);
SET host = '(at)usna(dot)edu';
November 29th, 2013, 04:37 AM
There are multiple syntactical and logical problems in your code.
1) You need to specify the type, whether it is a row trigger or statement trigger. In your case you need a row trigger.
2) If you want to modify the record that is inserted you must have a before trigger, not an after trigger.
3) To access the data being inserted you should use the new variable.
4) I assume that the excerpt
is meant to be an assignment.
The trigger body would rather be like
if new.amail is null then
set new.amail = concat(new.snb,'(at)usna(dot)edu');
November 29th, 2013, 04:54 AM
The new variable will replace the old Email in the column?
November 29th, 2013, 07:54 AM
Since the trigger is executed before the actual insert takes place, the value is modified prior to being inserted into the table.
November 29th, 2013, 11:49 AM
Oh yes. Ok, that makes sense. Thank you very much.
Originally Posted by swampBoogie