Syntax error 46010 indicated by migration tool

Question

question

Equus asked Sep 16, '21

Syntax error 46010 indicated by migration tool

Used the migration tool to upgrade SQL Server 2012 SP4 to 2019 and received notification that sever triggers had syntax errors, error code 46010. Since I relatively new in this arena and didn't author the code, I need some help. The code looks like this:

GO

/****** Object: Trigger [dbo].[T_TblEtl_ITrig] Script Date: 16-Sep-2021 13:04:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[T_TblEtl_ITrig] ON [dbo].[TblEtl] FOR INSERT AS

SET NOCOUNT ON

/* * PREVENT INSERTS IF NO MATCHING KEY IN 'TBL_XXXX' */

IF (SELECT COUNT(*) FROM inserted) !=

(SELECT COUNT(*) FROM TBL_XXX, inserted WHERE (TBL_XXX.sm_group_id = inserted.EtGrpId))

BEGIN

RAISERROR ( 50006,16,1)

ROLLBACK TRANSACTION

END

sql server

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2021-09-20T15:23:22Z

Equus answered Sep 20, '21

Thank you, I will give that a try.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2021-09-17T07:04:09Z

anthony.green answered Sep 17, '21

The old style join syntax is no longer valid so your

TBL_XXX, inserted

Needs to be rewritten as an inner join instead.

From TBL_XXX
INNER JOIN
Inserted

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Equus commented · Sep 22, 2021 at 10:12 PM

I am struggling to make this work. Very green in this arena. Any assistance is appreciated.

0 ·

anthony.green Equus commented · Sep 23, 2021 at 09:20 AM

/****** Object: Trigger [dbo].[T_TblEtl_ITrig] Script Date: 16-Sep-2021 13:04:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[T_TblEtl_ITrig] ON [dbo].[TblEtl] FOR INSERT AS
SET NOCOUNT ON
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'TBL_XXXX' */

IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM TBL_XXX INNER JOIN inserted ON (TBL_XXX.sm_group_id = inserted.EtGrpId))
BEGIN
RAISERROR ( 50006,16,1)
ROLLBACK TRANSACTION
END

0 ·

question