SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


help with a instead of trigger


help with a instead of trigger

Author
Message
gfoulks
gfoulks
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 10
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!
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28401 Visits: 39963
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gfoulks
gfoulks
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 10
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.
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28401 Visits: 39963
gfoulks (3/12/2014)
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.


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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gfoulks
gfoulks
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 10
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)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86984 Visits: 41107
gfoulks (3/12/2014)
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.


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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
gfoulks
gfoulks
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 10
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.
gfoulks
gfoulks
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 10
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;
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26277 Visits: 17553
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
gfoulks
gfoulks
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 10
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'
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search