Do I need a trigger?

Question

Do I need a trigger?

Hall of Fame

Points: 3915

October 30, 2020 at 6:01 pm

I want to test a condition during an insert or an update and only permit the insert or update if the condition is valid. The condition requires the evaluation of a value in multiple rows.

My scenario is this;

Multiple people can participate in the review of a submission. Only one person (at a time) may be designated as the "lead reviewer". I have a bit column in my table called Is_Lead_Reviewer. How an I prevent a user from inserting a new record or updating an existing record with Is_Lead_Reviewer set to TRUE when there is already a reviewer for that submission with Is_Lead_Reviewer set to TRUE?

Here's my table definition

CREATE TABLE [dbo].[t_Submission_Reviewer] (
[Submission_Review_Header_Id_FK] INT NOT NULL CONSTRAINT [FK_t_Submission_Reviewer~Submission_Review_Header_Id_FK_to_t_Submission_Review_Header] FOREIGN KEY REFERENCES [dbo].[t_Submission_Review_Header] ([Submission_Review_Header_Id])
,[Reviewer] VARCHAR(51) NOT NULL
,[Is_Lead_Reviewer] BIT NOT NULL CONSTRAINT [DF_Submission_Reviewer_Is_Lead_Reviewer] DEFAULT 'FALSE'
,[Submission_Reviewer_Created_On] DATETIME NOT NULL CONSTRAINT [DF_Submission_Reviewer_Created_On] DEFAULT GETDATE()
,[Submission_Reviewer_Updated_On] DATETIME NOT NULL CONSTRAINT [DF_Submission_Reviewer_Updated_On] DEFAULT GETDATE()
,[Submission_Reviewer_Updated_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_Submission_Reviewer_Updated_By] DEFAULT [dbo].[Get_User_Name]()
,[Submission_Reviewer_RowVersion] ROWVERSION NOT NULL
,[Submission_Reviewer_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_Submission_Reviewer_GUID] DEFAULT NEWID()
,CONSTRAINT [UIX_Reviewer_and_Review] PRIMARY KEY CLUSTERED ([Submission_Review_Header_Id_FK], [Reviewer])
)
GO

CREATE TRIGGER [dbo].[t_Submission_Reviewer_On_Update] ON [dbo].[t_Submission_Reviewer] FOR UPDATE AS
SET NOCOUNT ON
UPDATE TBL SET
TBL.[Submission_Reviewer_Updated_On] = GETDATE()
,TBL.[Submission_Reviewer_Updated_By] = [dbo].[Get_User_Name]()
FROM [dbo].[t_Submission_Reviewer]  TBL
JOIN inserted INS
ON TBL.[Submission_Review_Header_Id_FK] = INS.[Submission_Review_Header_Id_FK]
AND TBL.[Reviewer] = INS.[Reviewer]
GO

I've tried experimenting with an instead of update trigger but;

I don't know if that's the right/best solution.
Don't know how to update the record when the conditions are valid
Can't seem to make it work as desired.

Here's what I've tried;

ALTER TRIGGER [DBO].[T_SUBMISSION_REVIEWER_INSTEAD_OF_UPDATE] ON [DBO].[T_SUBMISSION_REVIEWER] INSTEAD OF UPDATE AS
SET NOCOUNT ON
IF UPDATE([IS_LEAD_REVIEWER]) AND (
SELECT
A.[SUBMISSION_REVIEW_HEADER_ID_FK]
FROM (
SELECT
T.[SUBMISSION_REVIEW_HEADER_ID_FK]
FROM [dbo].[t_Submission_Reviewer] T
WHERE T.[Is_Lead_Reviewer] = 'TRUE'
UNION ALL
SELECT
I.[SUBMISSION_REVIEW_HEADER_ID_FK]
FROM inserted I
WHERE I.[Is_Lead_Reviewer] = 'TRUE'
) A
GROUP BY A.[Submission_Review_Header_Id_FK]
HAVING COUNT([SUBMISSION_REVIEW_HEADER_ID_FK]) > 1) IS NOT NULL
BEGIN
RAISERROR('There can be only one lead revewer at a time', 16, 1)
ROLLBACK
BEGIN TRAN -- WITHOUT THIS THE ERROR MESSAGE "The transaction ended in the trigger.  The batch has been aborted." is thrown
END
ELSE
BEGIN
COMMIT TRAN
BEGIN TRAN -- WITHOUT THIS THE ERROR MESSAGE "The transaction ended in the trigger.  The batch has been aborted." is thrown
END
GO

Thanks in advance for any assistance you can provide.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

This sounds like business logic which should be handled in the stored proc which your front-end application calls when performing the INSERT.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 2

Phil;

Thanks for the response. That is an option. I was hoping to implement some kind of constraint at the database level that would be applied even if someone were doing updates outside of the front-end application - trying to make the database self-protecting, if you will.

frederico_fonseca SSCoach Points: 16167 More actions · Answer 3

you can create a unique filtered index on that column - it will prevent inserting more than 1 row with it set to true.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

I understand, but most SQL devs (including me) try to avoid triggers, as they can become difficult to manage, and sometimes have unexpected side effects.

The above is such a complicated piece of logic that users should be precluded from making any direct inserts, in my opinion. Though I appreciate that this is probably not a quick fix.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 5

frederico;

This is exactly what I was looking for! And I've learned something new. I didn't know about filtered indexes. I had considered a unique index but dismissed because I didn't think I could filter!

Phil; Your point is well taken and I'm sure it comes from the perspective of a wealth of first-hand experience. I'm still relatively new at DB development and learning new stuff all the time.

I'm not actually a SQL dev but have been thrust into the role in my own self-interest. (I have many years of experience with SQL Server - queries, views, SPs and such but just not much experience building tables). I provide BI and analytics services to my org. I'm often asked to work with poorly designed spreadsheets or Access databases. Data quality/integrity is often a problem. Occasionally I have the opportunity to replace a poorly designed information capture solution with something more rigorous. Right now I've been asked to add functionality to a pre-existing solution (Upscaled MS Access app) that has a number of data integrity issues. As I'm building tables to support the new functionality I'm trying to incorporate as many of the logical data validations as possible right in the database. I know that I'll likely wind up also applying them in the Access app but at least in the database no one (including me :-)) can do an end-around.

Thanks to both of you for your support. I wished I was able to give as much back as I get.