| Author |
Topic |
|
enatefox
Starting Member
4 Posts |
 Posted - 2008-01-17 : 16:49:27
|
| Hello,I hope I'm not double posting-- I couldn't find an answer elsewhere.Background:My page runs a SELECT query but sometimes throws an INSERT that I do not know beforehand that manipulates the data. I know the general query for the SELECT but do not know the INSERT at all. I need to catch it so I can find why it is being run. I need a trigger that can search queries for specific values as well as the type (insert/select/update).Problem:I need help making a trigger that will alert me when the page updates a specific column and passes a known query. So the page runs a query that I know already but since the parameters are going to be different (like the index value, etc.), I need the trigger to be flexible with matching it since the parameters will always change (like matching the column names and not requiring an exact search).Then if that has run AND an update query is run that has a matching value (the same index value that was selected), alert me.Does anyone know what to do or where to start? Thanks for any help you can give me. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-20 : 00:08:52
|
| How about third party sql auditing tool? |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2008-01-20 : 00:32:55
|
| when u say your page, u mean web page? need to add break points so u can step thru code and check values. what is programming lang? |
|
|
|
enatefox
Starting Member
4 Posts |
Posted - 2008-01-21 : 09:53:30
|
| I'm running a JRun4 Java server connected via ODBC to MSSQL 2000 database. There's a report that is running that should only be selecting but apparently it is throwing an insert somewhere. I want to catch that.The only thing I need to know is purely in the SQL Management app. What's the code for a trigger that can match certain column names before running? I don't need a trigger for every action, just ones that affect a specific table's specific columns. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2008-01-21 : 10:04:37
|
| u can add a trigger sure, but how are u going to know which web page did it?http://msdn2.microsoft.com/en-us/library/ms189799.aspxCreate Trigger aTriggeron yourTableFOR INSERTAS-- Do something here...i think you need to be stepping thru the application code though... |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2008-01-21 : 10:05:44
|
i guess if u cant find it, a trigger to disallow insert and raise an error will crash the web page making it easier to find (both in your web error logs and from users complaining)  |
|
|
|
enatefox
Starting Member
4 Posts |
Posted - 2008-01-21 : 10:08:17
|
| I can get the page and userid easily from the page. I'm looking to make a table that will store those values. I just need to know how to trigger it correctly. I can make one that checks for ALL types of activity, I need something similar to Grep, so I can match queries to a list and if successful, then do my evil bidding. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-21 : 16:14:48
|
| You can set update trigger on the table and check inserted table to find out which column is updated in the trigger. |
|
|
|
enatefox
Starting Member
4 Posts |
Posted - 2008-01-21 : 16:21:55
|
| So there would be no way to do that prior to the action of the trigger? That's kind of disappointing, I'd figure that there would be a way to search for specific phrases in the query.If anyone knows a way, please let me know. Thanks |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2008-01-21 : 16:28:41
|
| i think u r using wrong tool for the job. seems u wantg to run a profiler trace.but really, if u think the application is deleting data, u should be working in the app code, not the db.if u dont know what is deleting data, then an auditing trigger or a profiler trace or 3rd party audit software would be appropriate |
|
|
|
|
Conditional Triggering Specific Tables