Reputation: 8824
mysql create trigger ON INSERT
Suppose I have the following table
create table try ( name varchar(10), username varchar(10))
Is it possible to have a trigger 'ON INSERT' to set username=user() ?
I tried doing
create trigger userIns ON INSERT on try for each row set new.username=user();
but this gives some syntax error
I have seen documentation about BEFORE INSERT,AFTER INSERT. How about ON INSERT?
if not, whats my best bet?
Upvotes: 1
Views: 9814
Answers (4)
Reputation: 1
The problem is you need to set the delimiter to something other than ';' before you begin the trigger code. Put the new delimiter after 'end' and then change the delimiter back to ';' after you are done.
Upvotes: 0
Reputation: 8824
None of the above was my problem. I was really looking for an ON INSERT trigger - but it does not exist in MYSQL. So I used BEFORE INSERT and that works fine
create trigger userIns BEFORE INSERT on try for each row set new.CREATED_BY=user();
Upvotes: 0
Reputation: 18569
The syntax is :
CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name
FOR EACH ROW
BEGIN
...
END
Your syntax is wrong in trigger_time part.
Trigger activation time can be BEFORE or AFTER. You must specify the activation time when you define a trigger. You use BEFORE keyword if you want to process action prior to the change is made on the table and AFTER if you need to process action after change are made.
Try this :
create trigger userIns BEFORE INSERT on try for each row set new.username=user();
More : MYSQL Trigger Tutorial
Upvotes: 3
Reputation: 485
Correct Syntax is
Create Trigger trigger_name trigger_time trigger_event
on table_name
for each row
begin
....
end
Trigger_time => Before | After trigger_event => Insert | Update | Delete
Please check
create trigger userIns INSERT on try for each row begin set new.username=user() end;
Upvotes: 0