CODEWITHSUNDEEP
sqltriggerssybase
rxn1d
rxn1d

Reputation: 1266

How to disable cascading triggers in sybase 12.0?

I have 2 triggers for one specific table. First is for INSERT(address_create_tr) and second if for UPDATE(address_update_tr). So for example, insert trigger:

CREATE TRIGGER address_create_tr ON Address FOR INSERT AS
BEGIN 
    Update Address SET createdDate = getdate() FROM inserted WHERE Address.ID =inserted.ID 
END

The update trigger is the same except fact that it fires on UPDATE action and it updates updatedDate column. My problem rises when I insert new address row. Insertion fires address_create_tr trigger and it cascading invoke address_update_tr trigger (this is undesirable behaviour for me). This is happen when address_create_tr is fired, it updates createdDate field of specific row and when it updates createdDate it fires address_update_tr so updatedDate field is set too. So actual question:

How to disable cascading triggers in Sybase 12.0 ?

Thank you

Upvotes: 0

Views: 464

Answers (1)

Richard Crossley
Richard Crossley

Reputation: 586

Nested triggers can be disabled on a server-wide basis:

sp_configure 'allow nested triggers', 0

However you might want to speak to your DBA before asking for that, there may be some application implications that prevent that from happening.

Triggers can be written for multiple events, in the example provided, the trigger could be written to fire for insert and update as follows:

CREATE TRIGGER address_create_tr ON Address FOR INSERT, UPDATE AS

DECLARE
    @mode       char(1)

BEGIN

    select
            @mode = 'I'

    select
            @mode = 'U'
       from
            deleted            

    -- Only called for inserts 
    Update Address SET createdDate = getdate() FROM inserted WHERE Address.ID =inserted.ID 
        and @mode = 'I'

    -- Only called for updates
    Update Address SET updatedDate = getdate() FROM inserted WHERE Address.ID =inserted.ID 
        and @mode = 'U'

END
go

Whether the trigger is being called for insert or update is determined by the presence of rows in the deleted table. The deleted table contains the "before image" of the rows, so if there are no rows, the trigger is being fired for an insert.

The first update is only called when @mode = 'I', i.e. an insert, the second for an update.

The trigger can be optimised further to combine the functionality into a single update statement, this will improve performance:

CREATE TRIGGER address_create_tr ON Address FOR INSERT, UPDATE AS

DECLARE
    @mode       char(1)

BEGIN

    select
            @mode = 'I'

    select
            @mode = 'U'
       from
            deleted            

    Update Address SET 
            createdDate = 
                case 
                    when @mode = 'I' then getdate() else inserted.createdDate
                end,
            updatedDate =
                case
                    when @mode = 'U' then getdate() else inserted.updatedDate
                end
        FROM 
            inserted 
        WHERE 
            Address.ID =inserted.ID 

END
go

I used the following table definition and DML to test this:

create table Address (
    ID int, 
    line1 varchar(40), 
    createdDate datetime null, 
    updatedDate datetime null) 
go

insert into Address (ID, line1) values ( 1, 'First Insert') 
go
insert into Address (ID, line1) values ( 2, 'Second Insert') 
go
update Address set line1 = 'Updated address' where ID = 2
go

The results are as follows:

1> select * from Address
2> go
 ID          line1                                                          createdDate                     updatedDate                     
 ----------- ------------------------------------------ ------------------------------- ------------------------------- 
           1 First Insert                                           Feb 16 2015  7:35AM                            NULL 
           2 Updated address                                        Feb 16 2015  7:35AM             Feb 16 2015  7:35AM 

(2 rows affected)
1>

Tested on Sybase ASE 15.7 running on Linux.

1> select @@version
2> go
 ---------------------------------------------------------------------------------------------------------------------------
 Adaptive Server Enterprise/15.7.0/EBF 19495 SMP /P/x86_64/Enterprise Linux/ase157/2820/64-bit/FBO/Fri Sep 16 00:54:35 2011
(1 row affected)
1>

Upvotes: 1

Related Questions