Announcement

**Isomorphic** · 19 Jun 2019, 07:37

Take a look at the stack trace: SQLServer is throwing that error, not SmartGWT. There's no way for us to recover once that error has been thrown, as a response is never provided by the database here, we only get the exception.

As far as what's going on, we are doing standard JDBC usage of executeUpdate(), and it looks like Microsoft's JDBC driver just doesn't handle this kind of trigger being used for an update.

If you can't figure out how to adjust your trigger to make Microsoft's driver stop crashing with executeUpdate(), you could try issuing your INSERT as a fetch via direct JDBC. This would obviously be hacking around an MS driver bug, and we're only suggesting it because the exception they are throwing says that they returned a ResultSet, which is what you'd expect from a fetch.

This sample shows how to do your own JDBC calls in the midst of a SmartGWT-managed transaction.

**eschwegs** · 24 Jun 2019, 06:36

Thanks for the driver observation!

For anyone who bumps into this thread, I was able to solve it with two trigger code changes. The first was to ensure that there were no 'naked' select/insert/update statements other the one doing the actual intended table change. By that I mean I was able to do a query to find conflicting records, however I stuffed the results into a variable. That seemed to fix the drivers confusion as it probably obscured the intermediate results. Second, I was able to get the error message I wanted back through the software layers by using THROW, not RAISE.

Here is a working example of a the technique:

CREATE TRIGGER [dbo].[Test_Detail_Sticky_Waiver_InsteadOfInsertUpdate]
ON [dbo].[Test_Detail_Sticky_Waiver]
INSTEAD OF INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
DECLARE @action varchar(6),
@timeNow dateTime = getDate(),
@firstConflict varchar(100),
@debug tinyint = 0

IF EXISTS (SELECT * FROM INSERTED)
BEGIN

IF EXISTS (SELECT * FROM DELETED)
SET @action = 'update'
ELSE
SET @action = 'insert'
END

IF @action = 'insert' or @action= 'update'
BEGIN

-- find the first record having time overlaps with ones being inserted or updated.
set @firstConflict = (
SELECT TOP 1 'PK=' + CAST(tt.PK_Test_Detail_Sticky_Waiver as varchar(10)) + ' starting at session ' + tt_start.SessionID + ' ' + CAST(tt_start.CT_on_create as varchar(20))

FROM inserted ii
INNER JOIN Test_Detail_Sticky_Waiver tt ON tt.Test_Name = ii.Test_Name AND tt.State = ii.State AND tt.DetailType = ii.DetailType AND tt.DetailSubType = ii.DetailSubType
JOIN Test_Session tt_start ON tt.FK_Test_Session_Start = tt_start.PK_Test_Session
JOIN Test_Session ii_start ON ii.FK_Test_Session_Start = ii_start.PK_Test_Session
LEFT OUTER JOIN Test_Session_Stream ii_stream ON ii_stream.PK_Test_Session_Stream = ii_start.FK_Test_Session_Stream
LEFT OUTER JOIN Test_Session tt_end ON tt.FK_Test_Session_End = tt_end.PK_Test_Session
LEFT OUTER JOIN Test_Session ii_end ON ii.FK_Test_Session_End = ii_end.PK_Test_Session
WHERE
-- When updating a record (say by moving it's start of end time slightly) we create the query such that it doesn't detect it overlaps with it's original record still in the DB.
ii.PK_Test_Detail_Sticky_Waiver != tt.PK_Test_Detail_Sticky_Waiver

AND tt_start.FK_Test_Session_Stream = ii_start.FK_Test_Session_Stream
AND tt_start.FK_Test_Bench = ii_start.FK_Test_Bench

-- find time overlaps with current wivers
AND ( -- The start of the inserted reocrd lies with in an existing waiver
(tt_start.CT_on_create <= ii_start.CT_on_create AND ii_start.CT_on_create < ISNULL(tt_end.CT_on_create, @timeNow) )
OR
-- The end of the inserted record lies with in an existing waiver
(tt_start.CT_on_create < ISNULL(ii_end.CT_on_create, @timeNow) AND ISNULL(ii_end.CT_on_create, @timeNow) < ISNULL(tt_end.CT_on_create, @timeNow +1))
)
)

If @firstConflict IS NULL
GOTO CompleteTransaction

ROLLBACK TRANSACTION;

DECLARE @msg varchar(256) = 'A sticky wavier being ' + @action + 'ed overlaps existing waiver(s). The first being ' + @firstConflict ;
THROW 51000, @msg, 1;

GOTO EndTransaction

END -- IF @action = 'insert' or @action= 'update'

CompleteTransaction:

IF @action = 'insert'
INSERT INTO Test_Detail_Sticky_Waiver
--OUTPUT inserted.*
SELECT Inserted.Test_Name
,Inserted.FK_Test_Session_Start
,Inserted.FK_Test_Session_End
,Inserted.State
,Inserted.DetailType
,Inserted.DetailSubType
,Inserted.Rationale
,Inserted.FK_user_creator
,Inserted.FK_user_unwaiver
,Inserted.CT_on_create
,Inserted.CT_on_unwaive
FROM Inserted

IF @action = 'update'
UPDATE Test_Detail_Sticky_Waiver
SET Test_Name = Inserted.Test_Name
,FK_Test_Session_Start = Inserted.FK_Test_Session_Start
,FK_Test_Session_End = Inserted.FK_Test_Session_End
,State = Inserted.State
,DetailType = Inserted.DetailType
,DetailSubType = Inserted.DetailSubType
,Rationale = Inserted.Rationale
,FK_user_creator = Inserted.FK_user_creator
,FK_user_unwaiver = Inserted.FK_user_unwaiver
,CT_on_create = Inserted.CT_on_create
,CT_on_unwaive = Inserted.CT_on_unwaive
--OUTPUT inserted.*
FROM Inserted, Test_Detail_Sticky_Waiver
WHERE Inserted.PK_Test_Detail_Sticky_Waiver = Test_Detail_Sticky_Waiver.PK_Test_Detail_Sticky_Waiver

EndTransaction:

END

**Isomorphic** · 24 Jun 2019, 07:41

Thanks for posting this solution! Likely to help someone with a similar issue, and the techniques may apply to writing workable stored procedures for other database types too.

Announcement

insteadOfInsertUpdate db table trigger incompatibility

Comment

Comment

Comment