| Author |
Topic |
|
Delinda
Constraint Violating Yak Guru
315 Posts |
 Posted - 2010-06-01 : 14:06:28
|
Let's say i've table as follows,t1UID | Pwd ------------------------mike | 1234robin | 1890t2UID | LoginDte ------------------------ Let's say, i performed SQL as followselect * from t1 where UID='mike' and Pwd='1234' If rows returned, then i performinsert into t2 values('mike',getdate())it's possible to create trigger on select?If yes, how the trigger look's like? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Delinda
Constraint Violating Yak Guru
315 Posts |
Posted - 2010-06-01 : 14:13:29
|
quote:
Originally posted by tkizer
Triggers fire on DML actions only (INSERT/UPDATE/DELETE). If you want to insert rows based upon a select, then you could instead use a SQL Agent job that runs every minute. But can't you just modify the process that inserts into t1 so that it also inserts into t2?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Hi sir,How did I know, someone performselect * from t1 where UID='mike' and Pwd='1234' ?I need to keep track this row has been read by someone. This track record, will be inserted into t2 |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-01 : 14:24:53
|
| My suggestion would be to write it to some text file on a particular location within your application.Some kind of log entry.PBUH |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-01 : 15:01:10
|
| I just think it is an unnecessary overhead on the DB to store this kind of data also how will you define the table t2?I mean how many columns in it.One query may have about 15 filters while other one wont have any.What about the queries with JOINS in it?How is the OP going to store them?My suggestion would be write a simple method in DAL which writes the whole query statement to the text file.If the OP is looking just for a couple of tables then the method of OP is fine.But it would be a pain if he/she wants to do it for all the tables in the DB.PBUH |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-01 : 15:09:30
|
| So if there are 10 tables and OP wants to log all the selects of those 10 tables he/she would have create 10 more tables that matches DDL of the existing tables he/she wants to log.PBUH |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-01 : 15:27:13
|
| But I think what OP wants cannot be called in real sense as "AUDIT".But I wonder how will you audit for selects joining 4 -5 tables and having most of the columns in the select part?Wouldn't that be a little cumbersome.PBUH |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Delinda
Constraint Violating Yak Guru
315 Posts |
Posted - 2010-06-02 : 00:40:15
|
| tq for all the answer |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
After read rows, then perform insert
Tara Kizer