March 12, 2014 at 8:53 am
Hello,
I've searched the internet looking for examples of a instead of trigger that I would like to setup but I've not been able to find anything that would really fit what I'm trying to do. What I'm trying to do seems simple enough but I can't seem to find the sql query to make it work.
Basically I have a table that is used to capture some log data from an application. There are some rows that are being entered that I really don't need but I can't make an application change to prevent these rows from being entered in this table.
What I want is a instead of trigger so that if a inserted row meets a set criteria then the row is automatically deleted. Thus keeping these rogue entries from being inserted into my table.
Can anyone help with an example sql trigger that I could tweak to meet my specific needs?
Thanks!
March 12, 2014 at 9:05 am
gfoulks (3/12/2014)
Hello,I've searched the internet looking for examples of a instead of trigger that I would like to setup but I've not been able to find anything that would really fit what I'm trying to do. What I'm trying to do seems simple enough but I can't seem to find the sql query to make it work.
Basically I have a table that is used to capture some log data from an application. There are some rows that are being entered that I really don't need but I can't make an application change to prevent these rows from being entered in this table.
What I want is a instead of trigger so that if a inserted row meets a set criteria then the row is automatically deleted. Thus keeping these rogue entries from being inserted into my table.
Can anyone help with an example sql trigger that I could tweak to meet my specific needs?
Thanks!
based on your description, where you want to prevent some rows from being created, i'm not sure an instead of trigger is what you are after.
what would be wrong with deleting existing rows periodically, if they meet the criteria you were describing?
the trigger could insert data if it meets certain criteria, and ignore inserting if it did not meet criteria.
show us the table definition, and the criteria to include/exclude rows, and we can help, if you are sure that's the way to go.
Lowell
March 12, 2014 at 9:14 am
okay so the table in question has the following columns
DateTime
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.
March 12, 2014 at 9:28 am
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
March 12, 2014 at 9:31 am
apologies didn't realize you need the PK and types
There is a PK on DateTime(datetime), Script_Code(varcahr(4)) and ANI(varchar(15)
March 12, 2014 at 7:19 pm
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.
What on Earth do you have against the Columbus, Ohio area? 😉
Shifting gears back to the problem, is anything in this table encrypted?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2014 at 9:01 am
Jeff no issues with Columbus just using that area code as the example.
Anyway I created the trigger suggested replacing my example data with the data that I want to actually use and it is not working. I am still able to insert a new record when the ANI = '614xxxxxxx'
To answer your question no there is no encrypted data.
March 13, 2014 at 9:09 am
I created this trigger
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 ani <> '614xxxxxxx' or ani <> '614yyyyyyy';
END
I am still able to insert records with the ani = '614xxxxxxx' or ani = '614yyyyyyy;
March 13, 2014 at 9:23 am
gfoulks (3/13/2014)
I created this trigger
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 ani <> '614xxxxxxx' or ani <> '614yyyyyyy';
END
I am still able to insert records with the ani = '614xxxxxxx' or ani = '614yyyyyyy;
Look at your WHERE clause. You need to change the OR to AND.
'614xxxxxxx' is not equal to '614yyyyyyy'
Conversely
'614yyyyyyy' is not equal to '614xxxxxxx'
therefore when using OR they are both going to evaluate to true. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2014 at 9:28 am
but there are two different instances where I do not want records to be written to the table.
when the ANI = '614xxxxxxx' or if the ANI = '614yyyyyyy'
March 13, 2014 at 9:36 am
changed the or to and
that did the trick! Sorry for the newb question but I'm new to this and trying to learn.
March 13, 2014 at 9:37 am
if you are excluding two specific values:
WHERE ANI NOT IN('614xxxxxxx''614yyyyyyy' ) but that doesn't prevent 614aaaaaaa for example.
if you wanted to guarantee no alphanumerics(becauese people are lazily putting in repeating characters?")
WHERE ANI NOT LIKE '614[a-z]%'
can you put the filter up front int he applciaiton to prevent bad data there as well?
Lowell
March 13, 2014 at 9:37 am
gfoulks (3/13/2014)
changed the or to andthat did the trick! Sorry for the newb question but I'm new to this and trying to learn.
Glad that worked for you. Do you understand why? It is important you know why that works.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2014 at 10:08 am
It sort of makes sense but not entirely. I'm not evaluating the phone numbers together but instead want to treat them two separate values
Does the ANI = "6141112222' or does the ANI = '6141113333' if so then discard that record.
To me the OR condition seemed like the correct choice because I am evaluating multiple conditions for the same column. The AND would seem to be more appropriate if I were evaluating two or more columns (column a = 'x' and column b = 'y').
March 13, 2014 at 10:16 am
gfoulks (3/13/2014)
Jeff no issues with Columbus just using that area code as the example.Anyway I created the trigger suggested replacing my example data with the data that I want to actually use and it is not working. I am still able to insert a new record when the ANI = '614xxxxxxx'
To answer your question no there is no encrypted data.
The other folks seem to have taken care of the ANI issue. You do, however, have a larger problem. I'd never store SSN information in clear text. It's just begging for trouble and, if the company experiences a data theft, they won't take the fall for it... good folks like you will. I strongly recommend making properly salted encryption of the SSN a very high priority on your part even if it's the supposedly "safe" last 4 digits because you can still do a lot of damage to someone with that information alone.
The reason why I asked about encryption to begin with was because you might be able to get away with a staging table that has data moved from it to the real table once per minute by a job. What does that buy you? For one, it allows for fewer distinct inserts into a final indexed table (the staging table would typically have only 1 index on an IDENTITY PK). That also means faster inserts for the App, itself. The other thing is that the "move" process could be programmed to leave the bad rows behind in the staging table so that you have the opportunity to troubleshoot them if such a thing ever became necessary. It's also easier to delete from a smaller table if that's what you'd rather do with said bad rows.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply