Trigger To Prevent a User From Inserting a Blank Column

Question

Trigger To Prevent a User From Inserting a Blank Column

Teee-SQL

SSCrazy

Points: 2218
More actions
November 14, 2013 at 1:47 am

#279149

Hi Guys,
I need help with a trigger, I would like to prevent a user from inserting a blank string into a column.
At the moment the trigger below raises an error even when the txtDescription column is not blank.
Please help, I'm not sure what I'm missing:
CREATE TRIGGER trDtlIncident ON dbo.dtlIncident
AFTER INSERT
AS
IF EXISTS (SELECT * FROM dtlIncident
WHERE Convert(Varchar, txtDescription) <> '')
BEGIN
RAISERROR ('Incident description can not be blank.', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;
GO
Thanks

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Your trigger is checking the entire table, not the rows just inserted. So if there's any blank values anywhere in the table, the trigger will fail. You probably want to use the inserted table in the exists, rather than the actual table name.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

That said, a check constraint would probably be a better choice than a trigger here.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Teee-SQL SSCrazy Points: 2218 More actions · Answer 3

Teee-SQL

SSCrazy

Points: 2218

November 14, 2013 at 2:36 am

#1666297

Thank you, I'll try your suggestions 🙂

Teee-SQL SSCrazy Points: 2218 More actions · Answer 4

I've joined the inserted table to the actual table as it's using a text column and it works perfectly. Thanks

ALTER TRIGGER trDtlIncident ON dbo.dtlIncident

AFTER INSERT

AS

IF EXISTS (SELECT * FROM inserted i, dtlIncident inc

WHERE Convert(Varchar, inc.txtDescription) = ''

AND i.uidId = inc.uidId)

BEGIN

RAISERROR ('Incident description can not be blank.', 16, 1);

ROLLBACK TRANSACTION;

RETURN

END;

GO

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

I still recommend considering a check constraint instead of the trigger.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass