Message 3609: The transaction ended in the trigger

have a SQL 2000 trigger but when I run it SQL 2005 I get the following error

The transaction ended in a trigger

How do I get the trigger to run properly?

Thanks in advance...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trigPrevQuarters]
on [dbo].[table3]
after update
as
 
declare @int int
 
declare @year as varchar(4)
declare @UpdDatequarter varchar(12)
declare @monthVar varchar (2)
declare @month datetime
 
declare @qurter as varchar(12)
declare @combinedYm as varchar(6)
 
set @year = (select year(getdate()))
set @monthVar = (select month(getdate()))
 
if @monthVar <=3
set @year = @year-1 
 
set @month = (select case @monthVar%3
   	when 0 then  (dateadd( m, -3, getdate()))
   	when 1 then  (dateadd( m, -1, getdate()))
   	when 2 then  (dateadd( m, -2, getdate()))
 end )
 
if len(cast(month(@month)as varchar (2)))< 2 
set @monthVar = '0'+ cast(month(@month)as varchar (2))
 
set @combinedYm =  cast(@year as varchar(4)) + '' + @monthVar
 
set @int = (select distinct field1 from inserted)
set @qurter = (select field2 from inserted)
 
set @UpdDatequarter = (select field3 from table3 where field1 = @int) 
 
if (update (Value1) and @UpdDatequarter < @combinedYM)
begin
rollback transaction
 
end

Open in new window

LVL 1
HeitmanProgrammersAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>rollback transaction
that has indeed changed between sql 2000 and sql 2005

if you just want to just silently ignore that transaction, you need to run the trigger as "instead of", and run the transaction you want to run when you do NOT want to rollback.
0
 
HeitmanProgrammersAuthor Commented:
Since this is "AFTER UPDATE' trigger, I want to rollback the transaction when the "if" statement fails. In another words, when the conditions in the "if" statement is met, I would like to continue the update statement.

Would the code below be the right script fot it?
**************************************************************************************************
CREATETRIGGER [dbo].[trigPrevQuarters]
on [dbo].[table3]
INSTEAD OF UPDATE

as
 
BEGIN
declare @int int
 
declare @year as varchar(4)
declare @UpdDatequarter varchar(12)
declare @monthVar varchar (2)
declare @month datetime
 
declare @qurter as varchar(12)
declare @combinedYm as varchar(6)
 
set @year = (select year(getdate()))
set @monthVar = (select month(getdate()))
 
if @monthVar <=3
set @year = @year-1
 
set @month = (select case @monthVar%3
         when 0 then  (dateadd( m, -3, getdate()))
         when 1 then  (dateadd( m, -1, getdate()))
         when 2 then  (dateadd( m, -2, getdate()))
 end )
 
if len(cast(month(@month)as varchar (2)))< 2
set @monthVar = '0'+ cast(month(@month)as varchar (2))
 
set @combinedYm =  cast(@year as varchar(4)) + '' + @monthVar
 
set @int = (select distinct field1 from inserted)
set @qurter = (select field2 from inserted)
 
set @UpdDatequarter = (select field3 from table3 where field1 = @int)
 
if (update (Value1) and @UpdDatequarter < @combinedYM)
  'include the update statement to update the table
END
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
actually, it would be:


if NOT( (update (Value1) and @UpdDatequarter < @combinedYM))
  'include the update statement to update the table
END

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
HeitmanProgrammersAuthor Commented:
Why do I need to use NOT for the statement... I actually want to update the table when the condition in the if statement = TRUE.

Thanks.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in your original code, you had:

if (update (Value1) and @UpdDatequarter < @combinedYM)
begin
  rollback transaction
end

so, you want the opposite condition to actually execute the UPDATE, don't you?
0
 
HeitmanProgrammersAuthor Commented:
Understood... I had not copied my code correctly. Let me try this and I will get back to you.

Thanks.
0
 
HeitmanProgrammersAuthor Commented:
My questions is:

How do I reference the INSERTED table and include an update statement which is updating about 20 fields in a table?

Thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it would looks like this:
if NOT ( (update (Value1) and @UpdDatequarter < @combinedYM))
begin
  UPDATE t 
    SET col1 = i.col1
      , col2 = i.col2
      , col3 = i.col3
      , col4 = i.col4
       ... etc ...
    FROM [dbo].[table3] t
    JOIN INSERTED i
      ON i.primary_key = t.primary_key
end

Open in new window

0
 
HeitmanProgrammersAuthor Commented:
That's what I tried and it worked for me.

Thank you very much for your assistance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.