Take the 2-minute tour ×
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.

share|improve this 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. –  Kahn Jun 24 '13 at 6:26
add comment

closed as not a real question by Marian, Mark Storey-Smith, dezso, Max Vernon, RolandoMySQLDBA Jun 24 '13 at 14:55

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.

3 Answers

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.

share|improve this answer
add comment

As I know, Database level triggers can only be used to track database schema changes (DDL) not data changes (DML).

Check out : CREATE TRIGGER

share|improve this answer
add comment

if you are looking to tracking DDL changes at database level, here is how we handled it. You need individual triggers for monitor DML changes for objects in database.

create  trigger t_DBAudit 

on database

for

 create_procedure, alter_procedure, drop_procedure,

-- create_table, alter_table, drop_table,

 create_function, alter_function, drop_function 
-- create_view, alter_view, drop_view 
as

set nocount on

declare @data xml

select @data = EVENTDATA()

 insert into dbo.DBAudit(databasename, PostTime, eventtype, objectname, objecttype,sqlcommand,UserName)

values(

 @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),

 @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),

 @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),

 @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),

 @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),

 @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'varchar(max)'),

 @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.