What Is a Log Trigger?

Trigger (trigger) is a method that SQL server provides to programmers and data analysts to ensure data integrity. It is a special stored procedure related to table events. Its execution is not called by the program or manually started , But triggered by events, such as when a table operation (insert, delete, update) will activate its execution. Triggers are often used to enforce data integrity constraints and business rules. Triggers can be found in the DBA_TRIGGERS, USER_TRIGGERS data dictionary. SQL3 trigger is a statement that can be automatically executed by the system to modify the database.

The trigger has the following functions:

• You can force the data to be verified or converted before writing to the data sheet.

• When an error occurs in the trigger, the result of the change is undone.

• Some database management systems can use triggers for data definition language (DDL), called DDL triggers.

• The special instruction (INSTEAD OF) can be replaced according to specific circumstances.

^[1]

DELIMITER |

CREATE TRIGGER `<databaseName>` .` <triggerName> `

<[BEFORE | AFTER]> <[INSERT | UPDATE | DELETE]>

ON [dbo] <tableName> // dbo represents the owner of the table

FOR EACH ROW

BEGIN

--do something

END |

Triggers can implement cascading changes through related tables in the database, but these changes can be performed more efficiently through cascading referential integrity constraints. Triggers can force use

Constraints and triggers each have advantages in special cases. The main benefit of triggers is that they can contain complex processing logic using Transact-SQL code. Therefore, a trigger can support all the functions of a constraint; but it's not always the best way to give a function. Entity integrity should always be enforced at the lowest level through indexes, which are either part of the PRIMARY KEY and UNIQUE constraints or are created independently outside the constraints. Assuming functionality meets the functional requirements of the application, domain integrity should be enforced through CHECK constraints, while referential integrity (RI) should be enforced through FOREIGN KEY constraints. Triggers are extremely useful when the capabilities supported by the constraints do not meet the functional requirements of the application.

For example: Unless a REFERENCES clause defines a cascading reference operation, a FOREIGN KEY constraint can only validate a column value with a value that exactly matches the value in another column.

CHECK constraints can only validate column values against a logical expression or another column in the same table. If your application requires column values to be validated against columns in another table, you must use triggers. Constraints can only pass error messages through standard system error messages. If your application requires (or benefits from) custom information and more complex error handling, you must use triggers.

Triggers can implement cascading changes through related tables in the database; however, these changes can be performed more efficiently through cascading referential integrity constraints. Triggers can suppress or roll back changes that violate referential integrity, thereby canceling the attempted data modification. Such triggers may work when the foreign key is changed and the new value does not match the primary key. For example, you can create an insert trigger on titleauthor.title_id to roll back an insert if the new value does not match a value in titles.title_id. However, FOREIGN KEY is usually used for this purpose.

If there are constraints on the trigger table, they are checked after the INSTEAD OF trigger is executed but before the AFTER trigger is executed. If the constraint is broken, the INSTEAD OF trigger operation is rolled back and the AFTER trigger is not executed.

Can triggers be created on views? In SQL Server Books Online, there is no saying that triggers cannot be created on views, and the syntax explanation indicates that views can be created after CREATE TRIGGER is turned on. However, this does not seem to be the case, and many experts also say that triggers cannot be created on views. I also tested it specifically. Indeed, no matter whether it is a normal view or an indexed view, you cannot create triggers on it. Is this really the case?

But there is nothing wrong with it: when triggers are created on temporary or system tables, they are rejected.

A deep understanding of the FOR keyword of the FOR CREATE TRIGGER statement can be followed by one or more of INSERT, UPDATE, and DELETE, which means that triggers will not be triggered in other cases, including SELECT, TRUNCATE, WRITETEXT, and UPDATETEXT.

Related content An interesting application We saw that many registration systems cannot change the user name after registration, but this is mostly determined by the application. If you open the database table directly to change it, you can also change its user name. The rollback can be used to cleverly realize that the user name cannot be changed ... When the trigger internal statement fails ... In this case, the previous data change operation will be invalid. For example, if a trigger is triggered when data is inserted into the table, and a runtime error occurs inside the trigger at this time, an error value will be returned and the previous data insertion will be rejected. Statements that cannot be used in triggers Most T-SQL statements can be used in triggers, but some of the following statements cannot be used in triggers.

CREATE statements, such as: CREATE DATABASE, CREATE TABLE, CREATE INDEX, etc.

ALTER statements, such as: ALTER DATABASE, ALTER TABLE, ALTER INDEX, etc.

DROP statements, such as: DROP DATABASE, DROP TABLE, DROP INDEX, etc.

DISK statements, such as: DISK INIT, DISK RESIZE.

LOAD statements, such as: LOAD DATABASE, LOAD LOG.

RESTORE statements, such as: RESTORE DATABASE, RESTORE LOG.

RECONFIGURE

The TRUNCATE TABLE statement is not available in triggers in sybase!

Triggers are powerful and easily and reliably implement many complex functions, so why use them with caution. The trigger itself is not at fault, but the database and application maintenance will be difficult due to our abuse. In database operations, we can implement data operations through relationships, triggers, stored procedures, applications, etc ... rules, constraints,

insert Trigger insert

  create trigger tri_insert
 on student
 for insert
 as
 declare @student_idchar (10)
 select @ student_id = s.student_id from students
 inner join insertedion s.student_id = i.student_id
 if @ student_id = '0000000001'
 begin
 raiserror ('Cannot insert a student number of 1!', 16, 8)
 rollbacktran
 end
 go

update Trigger update

  create trigger tri_update
 on student
 for update
 as
 if update (student_id)
 begin
 raiserror ('Student ID cannot be modified!', 16, 8)
 rollbacktran
 end
 go

delete Trigger delete

  create trigger tri_delete
 on student
 for delete
 as
 declare @student_idvarchar (10)
 select @ student_id = student_id from deleted
 if @ student_id = 'admin'
 begin
 raiserror ('Error', 16, 8)
 rollbacktran
 end