Jiwa

I've seen quite recently a couple of sites having issues upgrading - and the cause has been triggers.

The actual error being reported during upgrade may be "subquery returned more than one result", "cannot start more transactions in manual or distributed mode" typically... but depending on what the trigger was trying to do, and what the script was trying to do that invoked the trigger, the messages will vary.

I would recommend disabling all triggers before upgrading a database, and re-enabling them afterwards. There may also be some manual scripts you would need to run before you re-enable the trigger - depending on what the trigger was supposed to do - which may be required to leave data in a consistent state.

Jiwa only has one trigger at this time, and it's days are numbered (DB_Trans.SetDueDate). I've found triggers to be problematic in trouble-shooting, as the symptoms are usually some strange error which does not indicate a trigger is involded.

Anyway, I've taken the liberty of writing a script which will disable all triggers, and another which will re-enable them.

This script disables all triggers - run it before upgrading - it will keep a list of all triggers in a table JIWA_Triggers_Listing.

Code: Select all

IF NOT EXISTS(SELECT TOP 1 * FROM sysobjects WHERE Name = 'JIWA_Triggers_Listing')
BEGIN
   CREATE TABLE JIWA_Triggers_Listing
   (
      RecID CHAR(36),
      TriggerName VARCHAR(500),
      TableName VARCHAR(500),
      Enabled BIT
   )
END

DELETE FROM JIWA_Triggers_Listing

INSERT INTO JIWA_Triggers_Listing
SELECT NewID(), object_name(deltrig), object_name(id), 1 FROM sysobjects WHERE xtype ='U' AND deltrig > 0

INSERT INTO JIWA_Triggers_Listing
SELECT NewID(), object_name(instrig), object_name(id), 1 FROM sysobjects WHERE xtype ='U' AND instrig > 0

INSERT INTO JIWA_Triggers_Listing
SELECT NewID(), object_name(updtrig), object_name(id), 1 FROM sysobjects WHERE xtype ='U' AND updtrig > 0

DECLARE @SQL VARCHAR(1000)
DECLARE @RecID CHAR(36)
DECLARE @TriggerName VARCHAR(500)
DECLARE @TableName VARCHAR(500)

WHILE EXISTS(SELECT TOP 1 * FROM JIWA_Triggers_Listing WHERE Enabled = 1)
BEGIN
   SELECT TOP 1 @RecID = RecID, @TriggerName = TriggerName, @TableName = TableName FROM JIWA_Triggers_Listing WHERE Enabled = 1
   SET @SQL = 'DISABLE TRIGGER ' + @TriggerName + ' ON ' + @TableName
   EXEC(@SQL)
   UPDATE JIWA_Triggers_Listing SET Enabled = 0 WHERE RecID = @RecID
END


And this is the script to run to re-enable the previously disabled triggers.

Code: Select all

DECLARE @SQL VARCHAR(1000)
DECLARE @RecID CHAR(36)
DECLARE @TriggerName VARCHAR(500)
DECLARE @TableName VARCHAR(500)

WHILE EXISTS(SELECT TOP 1 * FROM JIWA_Triggers_Listing WHERE Enabled = 0)
BEGIN
   SELECT TOP 1 @RecID = RecID, @TriggerName = TriggerName, @TableName = TableName FROM JIWA_Triggers_Listing WHERE Enabled = 0
   SET @SQL = 'ENABLE TRIGGER ' + @TriggerName + ' ON ' + @TableName
   EXEC(@SQL)
   UPDATE JIWA_Triggers_Listing SET Enabled = 1 WHERE RecID = @RecID
END

If you just want to list all triggers in the database (and this is a useful one to have when troubleshooting the more esoteric problems we sometimes see in day-to-day Jiwa), this query just lists them :

Code: Select all

SELECT object_name(id) [Table Name],
      object_name(deltrig) [Delete Trigger],
      object_name(instrig) [Insert Trigger],
      object_name(updtrig) [Update Trigger]
FROM sysobjects
WHERE xtype ='U'
AND (deltrig > 0 or instrig > 0 or updtrig > 0)

Thanks Mike that will be most helpful.

Here is an updated set of scripts:

This script disables all triggers - run it before upgrading - it will keep a list of all triggers in a table JIWA_Triggers_Listing.

Code: Select all

IF NOT EXISTS(SELECT TOP 1 * FROM sysobjects WHERE Name = 'JIWA_Triggers_Listing')
BEGIN
   CREATE TABLE JIWA_Triggers_Listing
   (
      RecID CHAR(36),
      TriggerName VARCHAR(500),
      TableName VARCHAR(500),
      Enabled BIT
   )
END

DELETE FROM JIWA_Triggers_Listing

INSERT INTO JIWA_Triggers_Listing
SELECT NewID(), name, object_name(parent_id), CASE is_disabled WHEN 0 THEN 1 ELSE 0 END  FROM sys.triggers WHERE is_disabled = 0

DECLARE @SQL VARCHAR(1000)
DECLARE @RecID CHAR(36)
DECLARE @TriggerName VARCHAR(500)
DECLARE @TableName VARCHAR(500)

WHILE EXISTS(SELECT TOP 1 * FROM JIWA_Triggers_Listing WHERE Enabled = 1)
BEGIN
   SELECT TOP 1 @RecID = RecID, @TriggerName = TriggerName, @TableName = TableName FROM JIWA_Triggers_Listing WHERE Enabled = 1
   SET @SQL = 'DISABLE TRIGGER [' + @TriggerName + '] ON ' + '[' + @TableName + ']'
   EXEC(@SQL)
   UPDATE JIWA_Triggers_Listing SET Enabled = 0 WHERE RecID = @RecID
END


And this is the script to run to re-enable the previously disabled triggers.

Code: Select all

DECLARE @SQL VARCHAR(1000)
DECLARE @RecID CHAR(36)
DECLARE @TriggerName VARCHAR(500)
DECLARE @TableName VARCHAR(500)

WHILE EXISTS(SELECT TOP 1 * FROM JIWA_Triggers_Listing WHERE Enabled = 0)
BEGIN
   SELECT TOP 1 @RecID = RecID, @TriggerName = TriggerName, @TableName = TableName FROM JIWA_Triggers_Listing WHERE Enabled = 0
   SET @SQL = 'ENABLE TRIGGER [' + @TriggerName + '] ON [' + @TableName +']'
   EXEC(@SQL)
   UPDATE JIWA_Triggers_Listing SET Enabled = 1 WHERE RecID = @RecID
END


If you just want to list all triggers in the database (and this is a useful one to have when troubleshooting the more esoteric problems we sometimes see in day-to-day Jiwa), this query just lists them .

Code: Select all

SELECT object_name(parent_id) [TableName], name [TriggerName], CASE is_disabled WHEN 0 THEN 1 ELSE 0 END [Enabled] FROM sys.triggers