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

CHECK CONSTRAINT vs. TRIGGER Expand / Collapse
Author
Message
Posted Monday, October 8, 2012 10:58 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 19, 2014 10:10 AM
Points: 526, Visits: 1,163
Hi,

If I have a CommonInformation table that can store other entities data, and entities can be Customers, Suppliers, whatever.. the relation is made by EntityType and EntityId, but since the source can be multiple tables a FOREIGN KEY can't handle this. (I know this isn't the perfect structure, probably EntityTable with EntityType to separate customers from suppliers, etc.. would be better and the CommonInfo table would only be "connected" by Id but the design is already made).
A way of assuring integrity could be done by creating CHECK CONSTRAINT on the CommonInfo table with a function to assure the entity exists, or create a TRIGGER to also do that, no need for the function here.
ALTER FUNCTION [dbo].[CheckEntityExists](@EntityType INT, @EntityId INT) RETURNS INT AS
BEGIN
DECLARE @ret INT = 0
SELECT @ret = COUNT(*) FROM (SELECT 1 Record FROM Customers WHERE @EntityType = 0 AND Id = @EntityId UNION ALL SELECT 1 FROM Suppliers WHERE @EntityType = 1 AND Id = @EntityId) t
RETURN @ret
END
GO
ALTER TABLE [dbo].[CommonInfo] WITH CHECK ADD CONSTRAINT [CheckEntityExistsConstraint] CHECK (([dbo].[CheckEntityExists]([EntityType],[EntityId])>(0)))
GO
ALTER TABLE [dbo].[CommonInfo] CHECK CONSTRAINT [CheckEntityExistsConstraint]
GO

The TRIGGER would do the same as the function...
Which is better to use? Should triggers be avoided?!

I know there's a BIG problem using this since a DELETE on an Entity table will not check data on CommonInfo..
A TRIGGER would have to be written FOR DELETE on every Entity table...

Another solution, but this on is much harder to implement on our system since it involves rewriting the code is to create an insert/update GeneralInfo Stored Procedure and have it check the integrity and also have a SPs for every DELETE on Entities and also check there for GeneralInfo or simply delete it also..

But for now my question is, cause of my limitations rewriting VB6 code, is CHECK CONSTRAINT better than TRIGGER?

Thanks,
Pedro




If you need to work better, try working less...
Post #1369957
Posted Monday, October 8, 2012 11:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
I don't think it really matters which one you use in this case. I'd probably go with a trigger, because I dislike complex constraints, but that's just a personal preference, not something that's got solid theory behind it.

The better solution would be rebuild the database and normalize it standardly. You already mentioned you won't have the opportunity to do that, so it won't matter. You're stuck with duct-taping it together, and which color of duct-tape (constraint vs trigger) is really unimportant.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1369961
Posted Monday, October 8, 2012 1:37 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:52 AM
Points: 438, Visits: 909
If you anticipate high volume row operations on the table (e.g.: modifying or adding 1,000s of rows in one statement), then a trigger is better.

Check constraints are fired with each row. Triggers can be written so all inserted rows are checked as a single batch and that is often faster. Also, a trigger can be written so it is only fired if some column(s) of interest are set/modified.

Post #1370013
Posted Monday, October 8, 2012 1:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 7,140, Visits: 12,764
GSquared (10/8/2012)
The better solution would be rebuild the database and normalize it standardly.

+1

You do have a path to refactor the database and not change a line of VB6 code. It would involve refactoring the tables that currently contain multiple entities (Entity and EntityType, dead giveawy on the naming ) and then creating VIEWs with INSTEAD TRIGGERS on them to handle the create, update and delete operations in your now-normalized base tables. Your VB6 will not know the difference.

As for CONSTRAINT or TRIGGER, pick your poison. They'll both be a challenge to develop, test and support.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1370025
Posted Monday, October 8, 2012 2:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 7,140, Visits: 12,764
antonio.collins (10/8/2012)
a trigger can be written so it is only fired if some column(s) of interest are set/modified.

Not entirely true. A trigger fires for every qualifying DML action. For INSERT and UPDATE actions it's true we have the UPDATE() function to check whether the DML operation included a reference to a specific column but that does not preclude the firing of the trigger, i.e. if the trigger is enabled we always incur the overhead of the trigger firing.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1370028
Posted Tuesday, October 9, 2012 3:47 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 19, 2014 10:10 AM
Points: 526, Visits: 1,163
antonio.collins (10/8/2012)
If you anticipate high volume row operations on the table (e.g.: modifying or adding 1,000s of rows in one statement), then a trigger is better.

Check constraints are fired with each row. Triggers can be written so all inserted rows are checked as a single batch and that is often faster. Also, a trigger can be written so it is only fired if some column(s) of interest are set/modified.


I made some test and the CHECK CONSTRAINT is faster than TRIGGER:
CREATE TABLE One (Id INT NOT NULL IDENTITY, Qnt INT NOT NULL)
GO
CREATE TABLE Two (Id INT NOT NULL IDENTITY, Qnt INT NOT NULL)
GO

DECLARE @i INT = 1
WHILE @i < 1000
BEGIN
INSERT INTO One (Qnt) VALUES ( 1)
INSERT INTO Two (Qnt) VALUES (1)
SET @i = @i + 1
END
GO
ALTER TABLE [dbo].One WITH CHECK ADD CONSTRAINT [CheckQntValues] CHECK (Qnt <= 300)
GO
ALTER TABLE [dbo].One CHECK CONSTRAINT [CheckQntValues]
GO
ALTER TRIGGER Trg_Two ON Two FOR UPDATE
AS
BEGIN
IF EXISTS (SELECT 1 FROM inserted i WHERE i.Qnt > 300)
BEGIN
ROLLBACK TRANSACTION
PRINT 'Error qnt'
END
END
GO

SET STATISTICS IO ON
SET STATISTICS TIME ON
PRINT '--- BEGIN ---'
UPDATE One SET Qnt = qnt * Id WHERE Id < 200
PRINT '--- SWITCH ---'
UPDATE Two SET Qnt = qnt * Id WHERE Id < 200
PRINT '--- END ---'

The CONSTRAINT takes " SQL Server Execution Times: CPU time = 0 ms, elapsed time = 2 ms."
and the TRIGGER:
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 3 ms.

The trigger takes a lot more time...
I made a query so it would fail and the CHECK CONSTRAINT also rolled back the entire transaction (don't know if this means that is just to a row by row operation...).
Or for simple operations (like this example) the CHECK CONSTRAINT is better and for more complex operations that would make the CHECK CONSTRAINT use function the best is TRIGGERs?

Thanks,
Pedro




If you need to work better, try working less...
Post #1370268
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse