Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

As far as I understand Trigger can be written for the table of the database. Now I want the trigger for database. The database can contain multiple table, If there is any change even in one table the trigger will fire says update table(another database table) set status='database changed'.

Note: One trigger for whole database.

I hope you understand my requirement, If you have any suggestion instead of trigger let me know.

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.
If this question can be reworded to fit the rules in the help center, please edit the question.

2

Depending on how much data and performance you need, Google contains some good examples of creating dynamic triggers. Basically, you could use one single script to create the same trigger on each and every table in the DB, which would basically do what you're asking for. But it would heavily go against all common sense, likely be a huge strain on performance, and wouldn't really capture most of the alter -type changes on tables let alone the DB anyway. So in my opinion, it would be better to focus on what the real need for this is and come up with an alternate solution instead.
–
KahnJun 24 '13 at 6:26

3 Answers
3

The requirement for a single trigger cannot be met, because there is no such feature available to use.

AFAIK, there is no alternative feature to do this type of thing centrally either. The closest feature would be a database audit, but that doesn't provide the ability to record a status anywhere -- it's only a record of what happened -- and it may not give the result you want either (see below).

You'll have to use table-level triggers, which are the only type of trigger that can be attached to DML statements.

I will point out that this type of strategy will carry overhead on all write activity in the database. If there's a high volume of write activity, or the triggers aren't well-written, you're in for a world of hurt. Also, just because a DML statement executed does not necessarily mean that the data changed: extra logic in each trigger will be required to handle this (presumably you want to make that distinction), so a centralized trigger wouldn't help consolidate things anyway.