SQL server three commonly used triggers

Three commonly used triggers for SQL server

1. Creation of triggers

When creating a trigger, you can first determine whether the trigger with the same name exists in the current database.
The trigger name of the sql server is saved in the sysobjects table. So to know whether it exists, just query the table before creating it.

 if not exists(select 1 from sysobjects where name='sqlserver_test (the trigger name to be created)'
  and type='tr')（If the current trigger does not exist）
 begin （Create a trigger）
  create trigger sqlserver_test（The trigger name to be created）
  on sqlserver_test_list（Triggered when the corresponding trigger event occurs in this table）
  for update (Select a trigger event)
  as
    if update(recommend) (judgetrigger sqlserver_testThis field in the table has changed)
   begin 
  select ‘This is a test of a trigger' （The execution statement is basicallySQLAll statements can be written，But please note that there is an execution process for the trigger. If the corresponding event of the trigger will trigger frequently, it is better to write less.，To avoid locking the event process。Cross-server query requires server openingMDSTCServe）
   end
 end
 else
 It's better not to write it here，It is impossible for the current trigger to exist. You can write a delete statement.。。。

trigger

As the name suggests, triggers when there is data insertion on the corresponding table

 create trigger sqlserver_test（The trigger name to be created）
  on sqlserver_test_list（Triggered when the corresponding trigger event occurs in this table）
  for insert (Triggered when there is an insertion on the table)
  as
    select recommend from Inserted （Take the field value of the insert row）
 end

trigger

As the name suggests, triggers when the corresponding table has row count deleted

 create trigger sqlserver_test（The trigger name to be created）
  on sqlserver_test_list（Triggered when the corresponding trigger event occurs in this table）
  for delete (Triggered when there is a deleted table)
  as
    select recommend from Deleted （Take the value of the deleted field）
 end

trigger

As the name suggests, triggers when the corresponding table has a row number updated

 create trigger sqlserver_test（The trigger name to be created）
  on sqlserver_test_list（Triggered when the corresponding trigger event occurs in this table）
  for update (Fired when the table is updated)
  as
    select recommend from Inserted（Get updated data）
 end

5. About the value

1. Insert operation (Insert)
The Inserted table has data, but the Deleted table has no data
hint:
You can use inserted this table to get the value of the required field to update other table data or insert it into other tables.
The most common is data transfer in intermediate tables
2. Delete operation (Delete)
The Inserted table has no data, the Deleted table has data
hint:
You can use Deleted to get the required field value to update other table data or insert it into other tables.
The most common thing is to delete data records or backup
3. Update operation (Update)
The Inserted table has data (new data), and the Deleted table has data (old data)
hint:
You can use Deleted to get the data values in the table before modification. The most common one is to make data modification records or backups. Use Inserted to obtain the modified data value. It is more common to obtain the function fields for other tables to perform corresponding operations.

This is the end of this article about the three commonly used triggers of SQL server. For more related SQL server trigger content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!