How are database triggers implemented inside the SQL database engine?

Question

How are triggers implemented inside the SQL database engine? I do not mean the definition of triggers of the SQL language level, but their basic implementations inside Oracle, SQL Server, MySQL, etc. How can the database engine scale the management of hundreds or thousands of triggers? Do they use a publish-subscribe model, for example, with an observer / listener template? Any pointers to relevant literature on this subject will also be appreciated.

I did google to "implement a database trigger", but all I found was information about the definitions of SQL triggers, which again do not want what I'm looking for.

Answer 1

Triggers are callbacks, so the implementation can be as simple as pointers to functions in C. Typically, a user does not expect a user-defined procedure code to be written to an RDBMS in C. You will need to maintain some other "higher level". Thus, the corresponding DSL programming pattern. The number of triggers (scalability) is not a problem in itself, because usually there is only one, at most two per table, and the DML event triggers only these. The implementation task is in another place: in areas of consistency, concurrency semantics.

Answer 2

You can study the source code of open source databases. For example Narrate PostreSql .

Answer 3

First, triggers are pieces of code that are fired when a specific event occurs in the database (for example, INSERT / UPDATE / DELETE in a specific table). Triggers are executed implicitly BEFORE or AFER in a DML statement, and triggers cannot be executed explicitly, like stored procedures.

There are also two types of triggers - STATEMENT LEVEL triggers and ROW LEVEL triggers.

STATEMENT LEVEL triggers fire before or after the statement is executed.

ROW LEVEL triggers are triggered before or after the operation is performed on each individual line affected by the operation.

So, we have 12 types of triggers:

1. BEFORE INSERT STATEMENT 2. BEFORE INSERT ROW 3. AFTER INSERT STATEMENT 4. AFTER INSERT ROW 5. BEFORE UPDATE STATEMENT 6. BEFORE UPDATE ROW 7. AFTER UPDATE STATEMENT 8. AFTER UPDATE ROW 9. BEFORE DELETE STATEMENT 10. BEFORE DELETE ROW 11. AFTER DELETE STATEMENT 12. AFTER DELETE ROW 

Multiple triggers can be encoded for an event with a priority order of the above execution.

Whenever we run a DML query (INSERT / UPDATE / DELETE) in the database, this query is launched in a transaction. Therefore, when the request is executed -

• Table locked
• The DBMS checks the triggers that are executed before the statement must be executed.
• Execute the actual SQL statement row by row.
• Searching for start EEF ROW for EACH ROW. If detected, completed.
• Check for errors. If there is, roll back the changes made by the instruction or its triggers.
• All AFTER EACH ROW triggers are found and executed.
• AFTER STATEMENT triggers found and launched.

Different DBMSs manage transactions differently. See their documentation for more details.

Many DBMSs only store triggers in text format, and not as compiled stored procedures.

It is best to call stored procedures inside the trigger body, because stored procedures are much faster than triggers.