Need replacement for Triggers

Question

Need replacement for Triggers

vignesh.ms

Hall of Fame

Points: 3456
More actions
January 29, 2014 at 3:05 am

#288826

Please go through the below query...
CREATE TRIGGER TRG_UpdateSyncAcc
ON dbo.AccountSample
AFTER update AS
BEGIN
INSERT INTO AccountSample_Backup
SELECT * FROM INSERTED
END
CREATE TRIGGER TRG_InsertSyncAcc
ON dbo.AccountSample
AFTER Insert AS
BEGIN
INSERT INTO AccountSample_Backup
SELECT * FROM INSERTED
END
I need to track all the data in the AccountSample table. But AccountSample table is in some other server. I dont have much access on those tables. Is there any other way to achieve above by using linked servers ?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Trigger on AccountSample, Change data capture, Change tracking or SQL Audit. All require you to have fairly high permissions on the table, database or server. If you don't have them, ask your DBA to do the implementation.

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

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 2

This is other party table.. we do have only select permission ( even my DBA also).

Is there any other way to perform this?

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

I suppose you could write some job that fetched the entire contents of the table on a regular basis and compared with previous versions. Horribly inefficient though.

Someone's got to have admin-level access to that database.

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Try Red Gate's or ApexSQL's Data Compare tools. This is right up their alley, and both can be automated I believe. You should be OK with read-only permission on the primary table this way. As Gail mentions, this will be inefficient as the table size grows, especially if the secondary database is across a slow network link. If everything is local on Gb+ network, should be fine even with GBs of data. Note you need some form of uniqueness on the table for this check to be efficient.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service