DML triggers in SQL Server 2005 Express

Question

Post reply

DML triggers in SQL Server 2005 Express

enisha.jack

SSC Rookie

Points: 34
More actions
June 18, 2009 at 4:25 am

#210972

Hi,
I am new to this environment and most of what I learnt comes from msdn. I need to do auditing for dml changes made to a database. The audit tables are going to be in a separate database. I am using triggers to do this. From msdn I know that a trigger can reference objects from another database but I have not discovered how this is done in order to write my triggers.
Any suggestions? Your help will be much appreciated.
Much Thanks!!

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29423 More actions · Answer 1

1. create linked server on the production database pointing to Audit server.

2. In your trigger you can write something like this

insert into AuditServer.AuditDB.owner.AuditTable(list of columns.....)

select col1, col2..... coln from inserted/deleted

Pradeep Singh

enisha.jack SSC Rookie Points: 34 More actions · Answer 2

Thank you for your advice 🙂

But as I said I am new to this environment. I have no idea what is a linked server or how to do that.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Open Books Online (the SQL help file) and look up Linked Servers. Or, if you prefer the online version: http://msdn.microsoft.com/en-us/library/ms188279(SQL.90).aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

enisha.jack SSC Rookie Points: 34 More actions · Answer 4

enisha.jack

SSC Rookie

Points: 34

June 18, 2009 at 5:00 am

#1011620

Thank you both!

enisha.jack SSC Rookie Points: 34 More actions · Answer 5

enisha.jack

SSC Rookie

Points: 34

June 18, 2009 at 5:59 am

#1011659

🙁

ps. One Orange Chip Points: 29423 More actions · Answer 6

here is how you create linked server on the source database

sp_addlinkedserver 'Audit_server'

Once that is done, you can setup a test environment by creating a test table in the audit database, insert couple of records in this table and try accessing this table from prod db.

select * from audit_server.audit_db.dbo.test

if this runs well, try inserting few records from query window from the prod db

insert into audit_server.audit_db.dbo.test values(...values here..)

if this works fine, you can proceed towards your trigger.

Hope this helps!!

Pradeep Singh

Lowell SSC Guru Points: 323546 More actions · Answer 7

did i misread the question? i thought he wants audit triggers to write to a separate database,and didn't see where anything about a separate server.

I thought it would be a simple matter toi have the trigger write to auditDB.dbo.AuditTable from a trigger on Production.dbo.TableToAudit

might need to make the trigger EXECUTE AS a user with permissions to both databases, but that depends on his security setup, right?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ps. One Orange Chip Points: 29423 More actions · Answer 8

No, I think i misread database as server!!

In that case he doesnt need to bother abt linked server....

Thanks Lowell for pointing the mistake.

Pradeep Singh