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:
- ensure that the trigger fires regardless of what the package author does, or
- 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.