can''t call sp_settriggerorder from an SP

• Miquel Martí

  Ten Centuries

  Points: 1217

  More actions

  March 13, 2006 at 10:37 am

  #152825

  Hello!

  I'm creating triggers from a trigger for auditing pourposes, and I need to be sure that the audit triggers are the last ones to be executed (in case some audited table has other triggers).

  I'm trying to use sp_settriggerorder, but it doesn't seem to work when called from a procedure. 

  This sequence does throw an error on my machine.

  Please, help. Am I doing something wrong?

  Thanks a lot

   

  IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'Table1')

    CREATE TABLE Table1 (Cod int)

  GO

  IF EXISTS (SELECT * FROM sysobjects WHERE name = 'tI_Table1')

    DROP TRIGGER tI_Table1

  GO

  CREATE TRIGGER tI_Table1 ON Table1 FOR INSERT AS

    Print 'I''m a trigger!'

  GO   

  IF EXISTS (SELECT * FROM sysobjects WHERE name = N'sp_TriggerOrder')

    DROP PROCEDURE sp_TriggerOrder

  GO

  CREATE PROCEDURE sp_TriggerOrder

  AS

    sp_settriggerorder tI_Table1, 'Last', 'INSERT'

  GO

  Servidor: mensaje 170, nivel 15, estado 1, procedimiento sp_TriggerOrder, línea 4

  Line 4: Incorrect syntax near 'sp_settriggerorder'.

• Jo Pattyn

  SSC-Dedicated

  Points: 32520

  More actions

  March 13, 2006 at 11:58 am

  #626334

  sp_settriggerorder @triggername= 'tI_Table1' /*quotes!*/,@order= 'Last', @stmttype ='INSERT'

  *second you can put SET NOCOUNT ON at the beginning of your triggers to reduce network traffic

• Miquel Martí

  Ten Centuries

  Points: 1217

  More actions

  March 14, 2006 at 2:44 am

  #626439

  Thanks for the reply.

  The quotes doesn't seem to be the problem.

  As a workarroud I EXECUTE the sp_settriggerorder call as a string. This seems to change the scope, and it works.

  IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'Table1')

    CREATE TABLE Table1 (Cod int)

  GO

  IF EXISTS (SELECT * FROM sysobjects WHERE name = 'tI_Table1')

    DROP TRIGGER tI_Table1

  GO

  CREATE TRIGGER tI_Table1 ON Table1 FOR INSERT AS

    Print 'I''m a trigger!'

  GO   

  IF EXISTS (SELECT * FROM sysobjects WHERE name = N'sp_TriggerOrder')

    DROP PROCEDURE sp_TriggerOrder

  GO

  CREATE PROCEDURE sp_TriggerOrder

  AS

  DECLARE @SqlString varchar(200)

    SET @SqlString = 'sp_settriggerorder @triggername=''tI_Table1''

                                       , @order=''Last''

                                       , @stmttype=''INSERT'''

    Print @SqlString

    EXECUTE(@SqlString)

  GO

  Thanks!

• noeld

  SSC Guru

  Points: 96590

  More actions

  March 14, 2006 at 7:33 am

  #626499

  when you call an SP and it is not the first line in the batch you need to use exec

  ...

  CREATE PROCEDURE sp_TriggerOrder

  AS

   exec sp_settriggerorder 'tI_Table1', 'Last', 'INSERT'

  GO

  ...

  
  * Noel

• Miquel Martí

  Ten Centuries

  Points: 1217

  More actions

  March 16, 2006 at 4:51 am

  #626979

  Thankx noeld

  That was it