1

I have a table using a DML trigger to ensure data integrity. Although authors of SSIS packages, BULK INSERT queries, and bcp executions can enable DML trigger execution, which is disabled by default, I need a way to ensure data integrity regardless of whether the author remembers to enable trigger execution. How can I:

  1. ensure that the trigger fires regardless of what the package author does, or
  2. ensure data integrity using another method within the database?

The table itself is along the lines of:

CREATE TABLE [dbo].[MyTable]
(
    [MyId] BIGINT IDENTITY NOT NULL PRIMARY KEY,
    [StoreCode] VARCHAR(50) NOT NULL,
    [ItemCode] VARCHAR(50) NOT NULL,
    [StartDate] SMALLDATETIME NOT NULL,
    [EndDate] SMALLDATETIME NULL,
    [IsDeleted] BIT NOT NULL DEFAULT (0)
)

and the trigger:

CREATE TRIGGER [dbo].[MyTrigger]
ON [dbo].[MyTable]
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON
DECLARE @OverlapIds TABLE
(
    [MyId] BIGINT NOT NULL PRIMARY KEY
)

INSERT @OverlapIds (MyId)
SELECT i.MyId
FROM   inserted i
WHERE  i.IsDeleted = 0
AND    EXISTS (SELECT 1 FROM [dbo].[MyTable] m
               WHERE  m.IsDeleted = 0
               AND    i.PricingId != m.PricingId
               AND    i.StoreCode = m.StoreCode
               AND    i.ItemCode = m.ItemCode
               AND    (i.StartDate BETWEEN m.StartDate AND COALESCE(m.EndDate, '2079-06-06 00:00:00') OR
                       m.StartDate BETWEEN i.StartDate AND COALESCE(i.EndDate, '2079-06-06 00:00:00')))

IF EXISTS (SELECT 1 FROM @OverlapIds)
BEGIN
    -- Do some things, then:
    RAISERROR('Rows with IsDeleted = 0 and matching store code and item code values may not have overlapping start and end dates.', 16, 1)
    ROLLBACK TRANSACTION
END

Denying privileges on the table is not a practical solution in this case unless there is a specific privilege denial that would still allow valid data to be inserted or updated. Check constraints are unlikely to help because the business rule requires other rows to determine whether the rule has been violated. Uniqueness constraints are unlikely to help because they would not prevent the date overlap. Designing the table better is, unfortunately, not a practical option at this time. Upgrading SQL Server itself is not completely off the table, though the immediate problem is in SQL Server 2005.

  • 1
    What about forcing bulk commands to go through a stored procedure that executes as owner (allowing you to give them rights to execute the procedure without the underlying rights of direct table access)? Might be a pain to set up, but if you're giving people direct bulk access and you can't trust them to do it a certain way, that is less of a technical problem than a policy one. – Aaron Bertrand Mar 18 '15 at 17:24
  • Depending on the Login that is initiating the Bulk operation (and what role(s) they are in), you can DENY ALTER on the table since that permission is needed in order for the trigger(s) to not get fired. This won't work in situations where the Login is sa and/or dbo. Also, it seems that DDL triggers are not fired for whatever mechanism allows the triggers to be temporarily disabled (else they would have been an option). – Solomon Rutzky Mar 18 '15 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.