gfoulks (3/12/2014)
okay so the table in question has the following columnsDateTime
Acct_Number
SSN
Script_Code
Div_indicator
first_name
last_name
ani
terminate_area
transfer_area
Periodically we are getting records in this table where ani = 614xxxxxxx
We want to prevent all records with the ani = 614xxxxxxx from ever being inserted into the table.
We thought about doing a scheduled job and purging those records out on a schedule but we thought that if there was a large number of records it could cause some issues with performance and we also have a sync process where this table is being synced to another server which is used for reporting and we don't want to risk having a sync catch these records before our scheduled job had a chance to run and purge the records. It seemed like purging these records in realtime was the best option for us.
so where is the primary key on that table, then? Im assuming you left it out; the more info you provide, the better we can help.
what is the datatype of ani? varcahr i'm assuming, but it can make a difference on creating the right solution.
here's a rough example:
CREATE TRIGGER TR_SomeTableInsteadOfFilter ON SomeTable
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO SomeTable([DateTime],Acct_Number,SSN,Script_Code,Div_indicator,first_name,last_name,ani,terminate_area,transfer_area)
SELECT [DateTime],Acct_Number,SSN,Script_Code,Div_indicator,first_name,last_name,ani,terminate_area,transfer_area
FROM INSERTED WHERE LEFT(ani,3) <> '614' AND LEN(ani) = 10;
END
Lowell