CHECK CONSTRAINT vs. TRIGGER

  • 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...

  • 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

  • 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.

  • 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 :-D) 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

  • 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

  • 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...

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

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