Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

help with a instead of trigger Expand / Collapse
Author
Message
Posted Wednesday, March 12, 2014 8:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 12:49 PM
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!
Post #1550282
Posted Wednesday, March 12, 2014 9:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 12,922, Visits: 32,299
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1550295
Posted Wednesday, March 12, 2014 9:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 12:49 PM
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.
Post #1550305
Posted Wednesday, March 12, 2014 9:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 12,922, Visits: 32,299
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1550316
Posted Wednesday, March 12, 2014 9:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 12:49 PM
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)
Post #1550317
Posted Wednesday, March 12, 2014 7:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:47 PM
Points: 35,555, Visits: 32,151
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1550497
Posted Thursday, March 13, 2014 9:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 12:49 PM
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.
Post #1550766
Posted Thursday, March 13, 2014 9:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 12:49 PM
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;
Post #1550772
Posted Thursday, March 13, 2014 9:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 13,220, Visits: 12,698
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 Moden's 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)
Post #1550782
Posted Thursday, March 13, 2014 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 12:49 PM
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'
Post #1550784
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse