June 2, 2011 at 2:51 am
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
As I have no trigger on this table, I made the assumption that maybe I had too many characters in the SQL statement. However, when I remove the word "Scripture" from the following statement, it works.
Any assistance would be greatly appreciated.
Table Design
InspirationIDintUnchecked
Quotevarchar(5000)Checked
picture1varchar(200)Checked
Sortorderchar(10)Checked
CustomerIDintChecked
SQL Update statement
update tb_inspiration set Quote='Come and see the works of the Lord who has done fearsome deeds on earth; Psalm 46:9. Ascension of the Lord – The eleven disciples went to Galilee to the mountain to which Jesus had ordered them. When they saw him they worshipped but they doubted. Then Jesus approached and said toe them “All power in heaven and on earth has been given to me. Go therefore and make disciples of all nations baptizing them in the name of the Father and of the Son and of the holy Spirit teaching them to observe all that I have commanded you. And behold I am with you always until the end of the age.” Matthew 28:16-20. Scripture shows us in many convincing ways that Jesus was a live after he rose from the grave. Now after his Ascension it is our turn to show in many convincing ways that he is alive in us.',picture1='Be still and know that I am God.jpg',sortOrder = '02 ' where InspirationID = 2
June 2, 2011 at 3:01 am
Ok, playing around some more. It seems that the Trigger word, if I can put it like that is Script
Even changing the SQL Statement to following, gives me the same error.
update tb_inspiration set Quote='Script',picture1='Be still and know that I am God.jpg',sortOrder = '02 ' where InspirationID = 2
June 2, 2011 at 3:14 am
Are you absolutely certain there's no trigger? Query sys.triggers
SELECT * FROM sys.triggers AS t WHERE parent_id = object_id('tb_inspiration')
The insert you posted works fine for me...
CREATE TABLE tb_inspiration (
InspirationID int NOT NULL,
Quote varchar(5000) ,
picture1 varchar(200),
Sortorder char(10) ,
CustomerID int
)
INSERT INTO tb_inspiration (InspirationID) VALUES (2) -- to get a row to update
UPDATE tb_inspiration
SET Quote='Come and see the works of the Lord who has done fearsome deeds on earth; Psalm 46:9. Ascension of the Lord – The eleven disciples went to Galilee to the mountain to which Jesus had ordered them. When they saw him they worshipped but they doubted. Then Jesus approached and said toe them “All power in heaven and on earth has been given to me. Go therefore and make disciples of all nations baptizing them in the name of the Father and of the Son and of the holy Spirit teaching them to observe all that I have commanded you. And behold I am with you always until the end of the age.” Matthew 28:16-20. Scripture shows us in many convincing ways that Jesus was a live after he rose from the grave. Now after his Ascension it is our turn to show in many convincing ways that he is alive in us.',
picture1='Be still and know that I am God.jpg',
sortOrder = '02 '
WHERE InspirationID = 2
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2011 at 3:31 am
Ok, sorry, I am sure that you can see that I have never used Triggers before. I see there is a trigger, but can't make out where the varible banthis is coming from?
USE [xxxxx]
GO
/****** Object: Trigger [dbo].[tb_inspiration_UPD] Script Date: 06/02/2011 11:28:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[tb_inspiration_UPD] on [dbo].[tb_inspiration] for insert, update as
begin
if exists(select * from inserted, banthis where
Quote like banthis.phrase COLLATE Latin1_General_CI_AS or
picture1 like banthis.phrase COLLATE Latin1_General_CI_AS or
Sortorder like banthis.phrase COLLATE Latin1_General_CI_AS)
rollback transaction
end
June 2, 2011 at 3:51 am
banthis is a table in the same database where the [tb_inspiration] is located.
Obvioulsy, you tried to insert a value that exists in the phrase column of the banthis table. My wild guess would be the value '02 ' (sortOrder).
You could run the following code to check it:
SELECT * FROM banthis WHERE phrase LIKE '02 '
Edit: re-reading your original error description, it seems like the Quote value already exists.
June 2, 2011 at 5:15 am
Thanks guys, found and resolved the issue.
Seems someone else has been making changes without my knoweldge 🙁
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply