7 replies to this topic

[Bat0u89]

I made a trigger on a table which sets @error=1 if a condition is satisfied.
Now I want to perform every insert inside a transaction which will rollback if error=1.

set @error=0;

insert into shift values

("c0001","k_tamiou4","2011-4-23 14:00:00", "2011-4-23 22:00:00");

delimiter $$;

if @error>0 then 

    rollback;

else 

    commit;

end if;

$$

I get an sql syntax error 1064 near "if @error>0 then".
I searched all over the internet but still can't deal with the problem.
I will appreciate it if you reply in the next 2-3 hours as the deadline for my project is tomorrow...

[WingedPanther]

What database type is this for?

[Bat0u89]

Sorry didn't mention I use MySQL. From what I see on other forums MySQL doesn't support "if" statements inside scripts. Now I just set a key value to null inside a "before" trigger in order to stop the insert...crude...

[WingedPanther]

MySQL :: Can I place IF statement in trigger?

[Bat0u89]

I don't have a problem with "if" statements inside triggers, only outside.

[WingedPanther]

Are you trying to do this within a SQL statement, a database function, or something else?

[Bat0u89]

... :blink: I press the "create new scratch area for executing queries" in mysql workbench :P

[WingedPanther]

That means you're trying to issue it as a standalone query. Think of it this way:

You issue

set @error=0;

That defines a variable that immediately goes out of scope in the next statement.

Now you issue:

 insert into shift values ("c0001","k_tamiou4","2011-4-23 14:00:00", "2011-4-23 22:00:00"); 

At this point, the statement either succeeds or fails, but it isn't sending back a value in @error for you to work with.

Next you issue:

delimiter $$; 

So, you've changed the statement termination to $$ instead of ;

Now you enter:

if @error>0 then      rollback; else      commit; end if; $$

At this point, you're trying to evaluate a variable that hasn't been defined. I'm not sure if this statement, as a whole, is even valid SQL, though it's perfectly valid as part of a script.