locked
Need Help in migrating ROLLBACK trigger from SYBASE to SQL SERVER 2008 RRS feed

  • Question

  • I need to migrate some triggers in sybase to sql server 2008  and below is a sample programSYBASE:

    create table sample
    (id int)


    insert into sample values(99)
    insert into sample values(34)


    --create a trigger to control the id to be less than 100.Any value which exceeds the range will be rolled back

    create trigger tr_sample
    on sample
    for insert,update
    as

    if ( (select id from inserted)>100)
    begin
    print "You cannot Exceed the Range!"
    /*Roll back trigger statement will roll back only within the trigger .It executes locally */

    rollback trigger
    return
    end
     

    --Test statements

    begin tran
    insert into sample (id) values (10)
    insert into sample (id) values (104)
    insert into sample (id) values (25)
    commit trans

    Output:Only the values 10 and 25 will be inserted and 104 will be rolled out

    SQL SERVER 2008

    create table sample
    (id int)


    insert into sample values(99)
    insert into sample values(34)

    --equivalent trigger in sql server 2008

    CREATE

    trigger tr_sample

    on


    sample

    for

     

     

    insert,

    update

    as

    begin

     

     

     

    /*IF @@TRANCOUNT <>0 BEGIN
    BEGIN TRANS savepoint

     

    END

    ELSE BEGIN*/

     

     

    SAVE TRAN savepoint
     

    PRINT @@TRANCOUNT 
     

    --END 
     

    if ((select id from inserted)>100) begin 
     

    RAISERROR ('Range Exceeded', 16, 1) 
     

    ROLLBACK TRAN savepoint
     

    return 
     

    end 
     

     

    else 
     

    begin
     

    goto success
     

    end
     

     

     

     

     

    success:
     

    PRINT 'SUCCESS'
    END

    go

     


    --Batch statements

    go

    BEGIN


    TRANSACTION

    PRINT

     

     


    @@TRANCOUNT

     

    insert

    into sample (id) values (90

    )

     

    insert

    into sample (id) values (1314

    )

     

    PRINT


    @@TRANCOUNT

     

    insert

    into sample (id) values (9005

    )

     

    commit


    transaction

    go

     
    I tried implementing the same logic of roll back trigger of sybase and sql server but all the records are getting inserted though the record above 100 should be rejected.Can anyone help?

    Please let me know if there is any alternative way to implement roll back triggers in sql server 2008.

     

    Thanks in advance,

    Rizwan

     


    Monday, January 23, 2012 11:09 AM

Answers

  • Hi Jano,

    Thanks for the information.Finally I have implemented similiar logic using instead of trigger this time and it worked.

    CREATE

    TRIGGER dbo.tr_sample

     

    ON dbo.sample
     

     
     

    INSTEAD OF INSERT, UPDATE 
     

    AS
     

    /*Generated by SQL Server Migration Assistant for Sybase version 5.1.1108.*/ 
     

    IF ( 
     


    (

     

     

    SELECT inserted.id
     

    FROM inserted
     

    ) > 100) 
     

    BEGIN 
     

    RAISERROR('Range Exceeded',
    16

    ,
    1

     

    )
     

     

    END
     

    ELSE
     

    BEGIN
     

     

    insert into sample select inserted.id from inserted
     

     

    END
     

    GO


    Thanks,

    Rizwan

     

    • Edited by Rizwan Basha Wednesday, January 25, 2012 10:32 AM
    • Marked as answer by Rizwan Basha Wednesday, January 25, 2012 10:33 AM
    Wednesday, January 25, 2012 10:32 AM

All replies

  • Hi,

    Why do you want to use trigger instead of check constraint? Check this code below:

    USE tempdb;
    GO
    
    CREATE TABLE [sample]
    (id int);
    GO
    
    ALTER TABLE [Sample] ADD CONSTRAINT
    CC_RangeLimit CHECK (id < 100);
    GO
    
    insert into [sample] values (1)
    GO
    

     

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    • Edited by Janos BerkeMVP Monday, January 23, 2012 2:11 PM fixed contraint logic
    Monday, January 23, 2012 1:46 PM
  • Hi Janos,

    It is a simple program written by me to test the roll back trigger and not the implementation of logic.Please give importance to roll back trigger in this case.I need a sample program in sql server which has the implementation equivalent to sybase-roll back trigger.

     

    Thanks in advance,

    Rizwan

    Tuesday, January 24, 2012 4:51 AM
  • Hi Rizwan,

    I'm afraid your only option is check constraint after reviewing your code completly. If you rollback transaction in the trigger, you can do this for the complete batch not for individual rows.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog
    Tuesday, January 24, 2012 1:50 PM
  • Hi Jano,

    Let me explain you the context of the problem that I am facing now.I am using Microsoft sql server migration assistant for sybase for converison of sybase code to sql server 2008.There are many triggers (like 'for insert','for update') on the tables and each trigger has 'roll back trigger' statement at the end in sybase.

    As per sybase logic,Rollback trigger rolls back only the effect of the trigger and the statement that caused the trigger to fire and it leaves out the outer transaction as it is (please refer to the test statements in sybase code in my initial post for understanding ).

    During SSMA converison, it has failed to convert the statement 'roll back trigger' and it encountered an error "S2SS0002: Unsupported SQL statement –ROLLBACK TRIGGER cannot be converted"

    The program written by me is just to implement the concept of roll back trigger in sql server 2008 by using save points.I need to emulate the functionality of roll back trigger of sybase in sql server 2008.

    Could you please suggest any alternative or provide me a sample program which does the functionality of a 'ROLLBACK TRIGGER' in sybase

    Thanks ,

    Rizwan

     

    Wednesday, January 25, 2012 5:27 AM
  • Hi,

    I'm afraid there is no equivalent solution in SQL Server. There is a pretty good article however for a similar solution at here: http://dbbest.net/blog/?p=7

    I still believe, check contraint is much better in terms of data validation.

    Janos 


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog
    Wednesday, January 25, 2012 9:07 AM
  • Hi Jano,

    Thanks for the information.Finally I have implemented similiar logic using instead of trigger this time and it worked.

    CREATE

    TRIGGER dbo.tr_sample

     

    ON dbo.sample
     

     
     

    INSTEAD OF INSERT, UPDATE 
     

    AS
     

    /*Generated by SQL Server Migration Assistant for Sybase version 5.1.1108.*/ 
     

    IF ( 
     


    (

     

     

    SELECT inserted.id
     

    FROM inserted
     

    ) > 100) 
     

    BEGIN 
     

    RAISERROR('Range Exceeded',
    16

    ,
    1

     

    )
     

     

    END
     

    ELSE
     

    BEGIN
     

     

    insert into sample select inserted.id from inserted
     

     

    END
     

    GO


    Thanks,

    Rizwan

     

    • Edited by Rizwan Basha Wednesday, January 25, 2012 10:32 AM
    • Marked as answer by Rizwan Basha Wednesday, January 25, 2012 10:33 AM
    Wednesday, January 25, 2012 10:32 AM