Trigger to delete a record on insert? 1

[WilliamUT]

I am logging all proxy activity on my ISA server to an SQL database. I am getting tons of records that show up as anonymous and I want to delete them as soon as they hit the database. I have tried using a trigger to delete them on insert but of course you can't delete a record before it has been inserted. Is there anyway I can make my Insert trigger save the record so I can delete it? Or any other kind of solution to delete these records as they come in?
Thanks

 

[mrdenny]

Have the trigger do a rollback if the username column is anonymous.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLBill]

Denny's idea should work, unless you are doing BULK INSERTS of the data. Then it might rollback ones that you do want.

All else fails, you could always create a job that on a schedule runs:

DELETE FROM mytablename WHERE UPPER(username) = 'ANONYMOUS'

-SQLBill

 

[SQLSister]

No need to rollback. Create an instead of trigger. If the username is not anonymous, insert the record, else do nothing.

Questions about posting. See faq183-874

 

[WilliamUT]

can you show me what this Instead of trigger would look like? the table name is webproxylog and the field name is ClientUserName and i want it if it is anonymous not to even save

 

[SQLBill]

Refer to the BOL, use the Index tab, enter INSTEAD OF INSERT. Click on that for the information and examples.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875