|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Trigger to block and logI would like a trigger to both block updates and deletions as well as
log all information I can get. I really don't care what the update or deletion is. Thanks for any help. -John John Baima wrote:
> I would like a trigger to both block updates and deletions as well as To block an insert or update operation on a table, you can use a simple > log all information I can get. I really don't care what the update or > deletion is. Thanks for any help. > > -John trigger on each table as follows: Create Trigger MyTableTriggerIU on dbo.MyTable For Insert, Update as Begin ROLLBACK End In order to audit this information, you need to access the inserted table, but as soon as you roll back the transaction, you'll lose whatever DML operations you performed. As an option, you can create an instead of trigger. Because the instead trigger never actually inserts /updates any data, you can access the inserted table without a problem. In the example, I just use an audit table that mimics the main table with some added columns. Create Trigger MyTableTriggerIU on dbo.atest Instead of Insert, Update as Begin Insert into dbo.atestaudit ( col1, audituser, auditdate ) Select col1, SUSER_SNAME(), getdate() From Inserted End
Show quote
"David Gugick" <david.gugick-nospam@quest.com> wrote: Thanks, that was very helpful. Can I collect other information for the>> I would like a trigger to both block updates and deletions as well as >> log all information I can get. I really don't care what the update or >> deletion is. Thanks for any help. >> >> -John > >To block an insert or update operation on a table, you can use a simple >trigger on each table as follows: > >Create Trigger MyTableTriggerIU on dbo.MyTable >For Insert, Update >as >Begin > ROLLBACK >End > >In order to audit this information, you need to access the inserted >table, but as soon as you roll back the transaction, you'll lose >whatever DML operations you performed. As an option, you can create an >instead of trigger. Because the instead trigger never actually inserts >/updates any data, you can access the inserted table without a problem. >In the example, I just use an audit table that mimics the main table >with some added columns. > >Create Trigger MyTableTriggerIU on dbo.atest >Instead of Insert, Update >as >Begin > Insert into dbo.atestaudit ( > col1, audituser, auditdate ) > Select col1, SUSER_SNAME(), getdate() From Inserted >End audit like where the net address of the connection. The problem is that many people share the same login and I'd like to know the specific user. -John John Baima wrote:
> Thanks, that was very helpful. Can I collect other information for the You should really look into giving all users their own logins. You can > audit like where the net address of the connection. The problem is > that many people share the same login and I'd like to know the > specific user. > get the MAC address from the sysprocesses table in the master database (see the net_address column). There's other information in that table as well.  |
|||||||||||||||||||||||
Other interesting topics