Triggers interfere with upgrades!

Find general Jiwa support here.

Triggers interfere with upgrades!

Postby Mike.Sheen » Thu Mar 27, 2008 7:19 pm

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)
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Triggers interfere with upgrades!

Postby jspohio » Tue Dec 02, 2008 10:25 am

Thanks Mike that will be most helpful.
jspohio
I'm new here
I'm new here
 
Posts: 4
Joined: Tue Dec 02, 2008 10:11 am

Re: Triggers interfere with upgrades!

Postby Scott.Pearce » Wed May 20, 2015 3:51 pm

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
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221


Return to Core Product Support

Who is online

Users browsing this forum: No registered users and 1 guest

cron