The goal is to create the ability to subscribe to a specific event (change) in the database. That is, when a certain field (s) change in a particular table (s), let's say notify some users by letter. Moreover, the creation of such a mailing should be performed on the web-page by the admin, i.e. without making changes to the database by hand.

So far, the only thing that comes to mind is the administrator's choice on the page of the table and fields, and the generation of the simplest trigger that will add an entry to the Events table, and already this service will be contacted and sent mail. If you wish a more complex event, give the admin fill in the body of the trigger.

Question: how many theoretically can I create triggers on the base \ table, how will this affect the performance?
How to maintain the current database scheme in the version control system?
Can you tell me a better way to track changes in the database?
sql server 2008r2, in the future 2012

    1 answer 1

    How many different questions you have :). In order:

    1) Judging by the fact that the existing requirement to send letters does not fit into the SQL code, subsequent extensions of events may also not fit. Therefore, it is obvious that the logic of events should not be in a sequel. The variant you have proposed is quite normal, especially if there are no stringent response time requirements to ping the Event label less often.

    However, you can look at this question from the other side: WHO and WHY writes directly to the database, since you are thinking about how to track changes in the database? If your application is writing there, track changes right there. If a third-party application is written there, let it write again through your API, and you will track changes. Think about it.

    2) Regarding the number of triggers in the database and on the table. Their number is limited only by the number of objects in the database. For the 2005 sequel, the size can be viewed here: http://www.sql-server-helper.com/sql-server-2005/maximum-capacity-specifications.aspx

    The performance of triggers affect just as much as you have there logic crammed. In your case, this will be an additional insert.

    3) Regarding the support of the current database scheme with a version control system (the question actually deserves a separate discussion). The common version is this: script your current schema, put it in the version control system. All subsequent changes to the scheme make an upgrade scripts and put there. Important:

    a) scripts must be rerunable,

    b) scripts must have version

    For example:

    Script1.0 - the basic version of the scheme

    Script1.1 - added Event table

    Script1.2 - added a new column to the Event table

    And so on. Thus, having driven the scripts sequentially from the beginning to the specific version, you get the base with the scheme corresponding to this version. Versions of scripts it makes sense to synchronize with the version of the application.

    • thanks for the answer. Several applications make changes to the database at once, access to which is not available, so rewriting under api will not work. Regarding the scheme: you need to somehow know what admin added triggers, so as not to search through the entire database. Say, when creating a trigger, put its code in a separate table? - gcoder
    • I do not understand why you need to know about the triggers? - andreycha