Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2011
    Posts
    36

    Answered: SIGNAL without rollback

    Is it possible to SIGNAL, information only, without a rollback? I have always used SIGNAL in a trigger to prevent an action from being completed, but never to simply notify or warn the user. I now have a need to notify the user of the result (not expecting to process any user response, which I know is impossible) but allow the action to be completed (i.e. commit the transaction anyway). Is it just a matter of using a different SQLState, or is it impossible?

  2. Best Answer
    Posted by mark.b

    "You can legally raise warnings in the compiled compound statements, but not in the inlined compound ones.
    There is a trick to avoid it, but you have to use an internal undocumented SP for that.
    Try to comment out the commented lines and comment the line with "signal sqlstate '01000' ..." statement.

    Code:
    create table test_trig2(i int) in userspace1@
    
    create or replace trigger test_trig2
    before insert on test_trig2
    referencing new as n
    for each row
    begin 
    --atomic
      if (n.i<0) then 
        signal sqlstate '75000' SET MESSAGE_TEXT = 'less than 0';
      elseif (n.i>0) then 
        signal sqlstate '01000' SET MESSAGE_TEXT = 'greater than 0';
    --    CALL SYSIBMINTERNAL.SQLEML_RAISE_ERROR(438, 'greater than 0', NULL);
      end if;
    end@
    "


  3. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    You can legally raise warnings in the compiled compound statements, but not in the inlined compound ones.
    There is a trick to avoid it, but you have to use an internal undocumented SP for that.
    Try to comment out the commented lines and comment the line with "signal sqlstate '01000' ..." statement.

    Code:
    create table test_trig2(i int) in userspace1@
    
    create or replace trigger test_trig2
    before insert on test_trig2
    referencing new as n
    for each row
    begin 
    --atomic
      if (n.i<0) then 
        signal sqlstate '75000' SET MESSAGE_TEXT = 'less than 0';
      elseif (n.i>0) then 
        signal sqlstate '01000' SET MESSAGE_TEXT = 'greater than 0';
    --    CALL SYSIBMINTERNAL.SQLEML_RAISE_ERROR(438, 'greater than 0', NULL);
      end if;
    end@
    Regards,
    Mark.

  4. #3
    Join Date
    Feb 2011
    Posts
    36
    Thank you, Mark. That is what I needed. I still wish there were some way to just raise a nicely-formatted message (i.e. without the "your system just blew up" wrapper, and with options for user choice). But I know that is reserved for the actual application, not for triggers. But knowing how to at least raise a "warning" without an automatic rollback allows a bit more control than just an error with rollback.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •