May 23, 2012 at 8:16 am
Hi,
I need to write an Instead of Insert trigger on a table that logs proxy information. There are a large amount of 'anonymous' records logged to this table and we need the trigger to only insert the non 'anonymous' records. I also want to keep track of how many 'anonymous' records it is NOT inserting to the table , so I have a counter in a another table called NumAnonymous.
This is what I have so far:
Create Trigger [dbo].[TrgInstdofInsert] on dbo.Proxy
Instead of insert
as
SET NOCOUNT ON
BEGIN
IF Exists (SELECT * FROM inserted WHERE ClientUserName = 'Anonymous')
BEGIN
UPDATE dbo.NumAnonymous SET NumAnonymous = NumAnonymous + 1;
END
ELSE
Insert INTO dbo.Proxy
select * from inserted
end
GO
I have tested this on a test DB and it seems to work, however if I apply it to production, I see the counter running but no records at inserted to the DB at all. Can anyone tell me what I am doing wrong?
May 23, 2012 at 8:32 am
Are you sure that there are clients that don't use the Anonymous login?
You didn't write anything about how you insert the data. If you insert it with insert select that have multiple records each time, then it is enough that at least one login used the Anonymous login, and you won't get any logging to your proxy table (in that case you'll also get wrong results in table NumAnonymous).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 23, 2012 at 8:40 am
The anonymous login records are unnecessary data, from what our Security team tells me. The vendor that supports the logging application cannot turn off the anonymous logging on their end, so they asked for a trigger instead. The DB grows very quickly and we frequently run into space issues. I only want to insert the records that not anonymous, so not sure why they would cause an issue in the insert?
May 23, 2012 at 9:03 am
This is really strange requirement but...
Your update to the table does not meet your stated requirements.
I also want to keep track of how many 'anonymous' records it is NOT inserting to the table , so I have a counter in a another table called NumAnonymous.
The following code will not do this correctly if there are 2 or more rows that meet your criteria.
UPDATE dbo.NumAnonymous SET NumAnonymous = NumAnonymous + 1;
Instead you should consider something like this:
UPDATE dbo.NumAnonymous SET NumAnonymous = NumAnonymous + (SELECT count(*) FROM inserted WHERE ClientUserName = 'Anonymous')
_______________________________________________________________
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/
May 23, 2012 at 9:06 am
You might also pitch back to whoever came up with this strange requirement that at some point somebody is going to ask about the rows that are explicitly NOT inserted. They are going to ask the question "I don't really care about the count, I am curious what DATA was not inserted". I can almost guarantee that question will be asked at some point. With the current structure you are literally throwing that information away.
_______________________________________________________________
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/
May 23, 2012 at 9:18 am
Thank you, I see where I was mistaken with my update statement. However, it still will not write any data to the proxy table. Could it be because its a intensive database? I do not understand if there is something wrong with my logic?
I agree, at some point they will ask to see all the records NOT being written to the DB, but at this point the amount of info it is logging is so large that they cannot weed out any useful data for reporting purposes.
May 23, 2012 at 9:24 am
For that matter your insert will not insert anything if there is at least 1 row with anonymous. I will see if I can tweak this a bit. Give me a few.
_______________________________________________________________
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/
May 23, 2012 at 9:29 am
See how this works for you.
Create Trigger [dbo].[TrgInstdofInsert] on dbo.Proxy Instead of insert as
BEGIN
SET NOCOUNT ON
IF Exists (SELECT * FROM inserted WHERE ClientUserName = 'Anonymous') --only update if there is a reason
UPDATE dbo.NumAnonymous SET NumAnonymous = NumAnonymous + (select COUNT(*) from inserted where ClientUserName = 'Anonymous')
IF Exists (SELECT * FROM inserted WHERE ClientUserName <> 'Anonymous') --only insert if there is a reason
Insert INTO dbo.Proxy
select * from inserted where ClientUserName <> 'Anonymous'
END
_______________________________________________________________
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/
May 23, 2012 at 9:40 am
That worked perfectly! Thank you so much for your help!
May 23, 2012 at 9:45 am
sdodia (5/23/2012)
That worked perfectly! Thank you so much for your help!
You're welcome. Thanks for letting me know that worked for you.
_______________________________________________________________
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/
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply