Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

trigger rollback, transaction bad syntax problem

syntax

  • Please log in to reply
7 replies to this topic

#1 Bat0u89

Bat0u89

    CC Regular

  • Member
  • PipPipPip
  • 25 posts

Posted 23 June 2011 - 07:48 AM

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...
  • 0

#2 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 23 June 2011 - 06:21 PM

What database type is this for?
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#3 Bat0u89

Bat0u89

    CC Regular

  • Member
  • PipPipPip
  • 25 posts

Posted 24 June 2011 - 04:23 AM

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...
  • 0

#4 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 24 June 2011 - 05:19 PM

MySQL :: Can I place IF statement in trigger?
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#5 Bat0u89

Bat0u89

    CC Regular

  • Member
  • PipPipPip
  • 25 posts

Posted 25 June 2011 - 04:31 AM

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

#6 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 25 June 2011 - 06:02 AM

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

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#7 Bat0u89

Bat0u89

    CC Regular

  • Member
  • PipPipPip
  • 25 posts

Posted 25 June 2011 - 08:07 AM

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

#8 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 25 June 2011 - 10:54 AM

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.
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/






Also tagged with one or more of these keywords: syntax

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download