Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trigger To Prevent a User From Inserting a Blank Column Expand / Collapse
Author
Message
Posted Thursday, November 14, 2013 1:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:47 AM
Points: 62, Visits: 224
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
Post #1514167
Posted Thursday, November 14, 2013 1:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:37 AM
Points: 40,596, Visits: 37,053
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 2008, MVP
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

Post #1514168
Posted Thursday, November 14, 2013 2:05 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:37 AM
Points: 40,596, Visits: 37,053
That said, a check constraint would probably be a better choice than a trigger here.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1514173
Posted Thursday, November 14, 2013 2:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:47 AM
Points: 62, Visits: 224
Thank you, I'll try your suggestions
Post #1514184
Posted Thursday, November 14, 2013 2:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:47 AM
Points: 62, Visits: 224
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
Post #1514192
Posted Thursday, November 14, 2013 3:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:37 AM
Points: 40,596, Visits: 37,053
I still recommend considering a check constraint instead of the trigger.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1514199
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse