Using Triggers to update data.

Question

Using Triggers to update data.

Simon AV

SSCommitted

Points: 1551
More actions
July 9, 2010 at 4:13 am

#237568

I have a trigger that I have created on a table that updates another table on insert. This is working fine. However I would like to amend it to update data in all databases in the server.
When I try to do this using a cursor it fails with 'a USE database statement is not allowed in a procedure or trigger' error message.
It also fails using msforeeachdb - I`m assuming for the same reason.
I don`t particualy want to code the trigger to have to look at database one by one, so is there a good way to achieve this ?

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

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

you need to change it to refer to each database individually, and use a full three part naming convention to get to each database, I think:

INSERT INTO Production.dbo.AuditTable ....FROM INSERTED

INSERT INTO SandBox.dbo.AuditTable ....FROM INSERTED

INSERT INTO Whatever.dbo.AuditTable ....FROM INSERTED

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!

Simon AV SSCommitted Points: 1551 More actions · Answer 2

I thought that might be the answer but I was hoping there was a more elegant way to do it. It means that I`ll have to amend the trigger as new databases are added to the server.

Blast !

Lowell SSC Guru Points: 323482 More actions · Answer 3

wouldn't it fail anyway if your table doesn't exist in the database as you are going thru them with a cursor?

if you explain your process, we might be able to offer an alternative. Why do you have to update a specific table in every database if a change in this table occurs?

why can't the other databases simply have a VIEW that points to that specific table instead?

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!

Simon AV SSCommitted Points: 1551 More actions · Answer 4

I can easily isolate just the databases that I am interested in via sysdatabase to ensure the trigger ran.

What I have is an old third party application that is no longer supported (suprise suprise). We have around 50 databases, one for each client we have on the software. Access to the databases is controlled via user logins. All of our users have access to all 50 databases depending on their role.

It has been decided that passwords have to be changed every 30 days - which is fine. But users are not that keen on having to do this in all 30 databases. I was hoping to create a trigger on the password update in one database to roll the new password accross all the databases.

It works fine if I just update one other database but I can`t make it work updating all databases.

I accept this isn`t the perfect solution, but it would make it easier for the users.

Lowell SSC Guru Points: 323482 More actions · Answer 5

thanks for the explanation!

yeah...you can use dynamic SQL in a trigger to do what you want, but if you have 50 databases, don't you need the same trigger on the other 49? so if i change my password in Database2 it is propigated everywhere else?

can there be a "KEN" in Database1 that is NOT the same as "KEN" in Database2, or that he has different settings in the other db compared to the first?

if it was possibleor not, i'd seriously consider removing the table in all other databases, and replacing it with a VIEW of the same name....it would centralize all the usernames/passwords/permissions you are storing in there....obviously you'd have to look at it, but that would go a long way to eliminating the multiple updates.

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!

Simon AV SSCommitted Points: 1551 More actions · Answer 6

A master table with views in each db may well be the way forward.

The user will always sit in each db. I`ll need to double check to ensure they all have the same security settings in each db. In which case that may be the way forward.

If the security settings are different then it may be the long way - code each db ! And yes you are right the trigger would need to be in each db. That isn`t hard to setup though, least of my problems !

Cheers for your help.

Lowell SSC Guru Points: 323482 More actions · Answer 7

ugg... i'm thinking that the same trigger in multiple databases will cause locking as soon as DB1 updates DB2 and fires the DB2 trigger, which in turn wants to update DB1.

maybe a service broker job or a job that the agent scans every x minutes to propigate the changes instead of a trigger would be better and faster right now....

if each row has a modified date in it, a scheduled job could look for changes in each db and push them out if they are new.

if there is NOT a modified column, then each db could have a trigger that puts a record in an audit table, and the job uses that audit table to push changes around.

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!

Simon AV SSCommitted Points: 1551 More actions · Answer 8

That hadn`t crossed my mind and you are absolutely right. Its a recipe for disaster. How to kill every database in one easy step.

I think I`ll either a. investigate the audit table idea, b. investigate a single table with views, c. inform the business that its breaks our security model and let them continue as they are now !

Cheers

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 9

Some questions:

1) Do you really need this update "in transaction / in process" ?

If a single update in database y doesn't succeed (e.g. locked) your whole trigger will fail !

Can you afford that ?

2) can this be done asynchrone ?

Use Service broker or a sqlagent job to handle your updates near-time / scheduled.

3) Keep in mind if one of your other databases is not available (for whatever reason), your source

database will nolonger accept changes to this object unless the trigger is disabled.

(you'll also need a "recovery" procedure for that)

4) Why aren't the other databases just querying your source table directly (using an alias / view)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me