April 18, 2012 at 5:20 am
Hi,
I have trigger that does check for null,blank PRCode and duplicates. It will just raise error when this happens. It works fine for single transaction but when update has a multiple row update it raise error
--> Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I cant add constraint for some old records has null values for this column. So new records to be added are just checked.
Heres my Trigger
Thanks in advance
CREATE TRIGGER [dbo].[CheckForPRCode] ON [dbo].[tblProduct]
FOR INSERT,UPDATE
AS
DECLARE @err_message varchar(255)
IF (SELECT ISNULL(i.PRCode,'') FROM inserted AS i) = ''
BEGIN
SET @err_message = 'No PR Code.'
RAISERROR (@err_message, 17,1)
ROLLBACK TRANSACTION
END
ELSE IF EXISTS(SELECT i.PRCode
FROM dbo.TBLPRODUCT as tP
JOIN inserted AS i
ON tP.PRCode = i.PRCode
Where i.PRCode <> ''
Group By i.PRCode
Having COUNT(*) > 1)
BEGIN
SET @err_message = 'Duplicate PR Code.'
RAISERROR (@err_message, 17,1)
ROLLBACK TRANSACTION
END
GO
April 18, 2012 at 5:33 am
How about replacing your IF (SELECT ISNULL(i.PRCode,'') FROM inserted AS i) = ''
with
IF exists ( SELECT * FROM inserted AS i where ISNULL(i.PRCode,'') = '' )
...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 18, 2012 at 5:54 am
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
--CREATE TABLE dbo.Exceptions
--(
--ExceptionId int IDENTITY(1,1) NOT NULL
--CONSTRAINT PK_Exceptions PRIMARY KEY
--,ExceptionDate datetime NOT NULL
--,ErrorNumber int NOT NULL
--,ErrorSeverity int NOT NULL
--,ErrorState int NOT NULL
--,ErrorProcedure nvarchar(126) NOT NULL
--,ErrorLine int NOT NULL
--,ErrorMessage nvarchar(2048) NOT NULL
--)
--GO
CREATE PROCEDURE dbo.ExceptionHandler
@ProcID int = NULL
,@ExtraMessage nvarchar(1000) = NULL
AS
SET NOCOUNT ON;
DECLARE @ErrorNumber int = ERROR_NUMBER()
,@ErrorSeverity int = ERROR_SEVERITY()
,@ErrorState int = ERROR_STATE()
,@ErrorProcedure nvarchar(126) = ERROR_PROCEDURE()
,@ErrorLine int = ERROR_LINE()
,@ErrorMessage nvarchar(2048) = ERROR_MESSAGE();
IF @@TRANCOUNT > 0
ROLLBACK;
IF @ProcID IS NOT NULL
SET @ErrorProcedure = N'[' + OBJECT_SCHEMA_NAME(@ProcID) + N'].[' + @ErrorProcedure + N']';
IF @ExtraMessage IS NOT NULL
SET @ErrorMessage = @ErrorMessage + NCHAR(13) + NCHAR(10) + @ExtraMessage;
---- Email alert
--DECLARE @mailbody nvarchar(4000) =
--'Error ' + CAST(@ErrorNumber AS varchar(20)) + ' occurred in procedure '
--+ COALESCE(@ErrorProcedure, 'NA') + ' at line ' + CAST(@ErrorLine AS varchar(20))
--+ ' with a severity of ' + CAST(@ErrorSeverity AS varchar(20))
--+ ' and a state of ' + CAST(@ErrorState AS varchar(20))
--+ '.' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
--+ @ErrorMessage;
--EXEC msdb.dbo.sp_send_dbmail
--@profile_name = 'YourProfile'
--,@recipients = 'you@you.com'
--,@importance = 'High'
--,@subject = 'SQL Exception'
--,@body = @mailbody;
---- write to log
--INSERT INTO dbo.Exceptions
--(
--ExceptionDate, ErrorNumber, ErrorSeverity, ErrorState
--,ErrorProcedure, ErrorLine, ErrorMessage
--)
--SELECT CURRENT_TIMESTAMP, @ErrorNumber, @ErrorSeverity, @ErrorState
--,COALESCE(@ErrorProcedure, N'NA'), @ErrorLine, COALESCE(@ErrorMessage, N'No Message');
RAISERROR
(
N'Error %d occurred in procedure %s at line %d. %s'
,@ErrorSeverity
,@ErrorState
,@ErrorNumber
,@ErrorProcedure
,@ErrorLine
,@ErrorMessage
);
GO
CREATE TRIGGER dbo.TR_tblProduct_PRCode
ON dbo.tblProduct
AFTER INSERT,UPDATE
AS
SET NOCOUNT ON;
BEGIN TRY
IF EXISTS
(
SELECT 1
FROM inserted
WHERE COALESCE(PRCode, '') = ''
)
RAISERROR ('No PR Code.', 17,1);
ELSE IF EXISTS
(
SELECT PRCode
FROM
(
SELECT PRCode
FROM inserted
UNION ALL
SELECT P.PRCode
FROM dbo.tblProduct P
WHERE EXISTS
(
SELECT 1
FROM inserted i
WHERE i.PRCode = P.PRCode
)
) D
GROUP BY PRCode
HAVING COUNT(PRCode) > 1
)
RAISERROR ('Duplicate PR Code.', 17,1);
END TRY
BEGIN CATCH
EXEC dbo.ExceptionHandler;
END CATCH
GO
April 18, 2012 at 5:56 am
@ALZDBA Thanks for the info but this is just for blank or null values.. It would not check for duplicates and if there is a multiple record update trigger wont work..
Sample update that I had the error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Update tblProduct set PRCode = (LEFT(ProductName, 3) + cast(ProductID as varchar (10))) WHERE PRCode is null
April 18, 2012 at 6:57 am
The trigger should be:
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER dbo.TR_tblProduct_PRCode
ON dbo.tblProduct
AFTER INSERT,UPDATE
AS
SET NOCOUNT ON;
BEGIN TRY
IF EXISTS
(
SELECT 1
FROM inserted
WHERE COALESCE(PRCode, '') = ''
)
RAISERROR ('No PR Code.', 17,1);
IF EXISTS
(
SELECT P.PRCode
FROM dbo.tblProduct P
WHERE EXISTS
(
SELECT 1
FROM inserted i
WHERE i.PRCode = P.PRCode
)
GROUP BY PRCode
HAVING COUNT(PRCode) > 1
)
RAISERROR ('Duplicate PR Code.', 17,1);
END TRY
BEGIN CATCH
EXEC dbo.ExceptionHandler;
END CATCH
GO
April 18, 2012 at 7:09 am
keep in mind to also check your inserted set for duplicates.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 18, 2012 at 8:45 am
ALZDBA (4/18/2012)
keep in mind to also check your inserted set for duplicates.
I was half thinking that for my original version but of course it is an AFTER trigger so one only needs to check the table.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply