DML AFTER UPDATE trigger creation principle
When a trigger is triggered, the system automatically creates a DELETED table or INSERTED table. The INSERTED table temporarily stores rows that have been inserted or updated, and the DELETED table temporarily stores rows that have been deleted or updated. The tables created in memory are read-only and cannot be modified.
How an UPDATE trigger works: First, the UPDATE statement is executed, second, the update trigger deletes the original data and backs up the deleted data to the deleted table, and third, new rows are inserted and backed up to the INSERTED table.
Instead of using SSMS database management tools to create DML add triggers directly, you can use T-SQL scripts to create DML add triggers.
DML AFTER UPDATE trigger created
Grammar:
Declare database references
Use database name;
go
Determine whether the trigger exists
If exists(select * from sysobjects where name= FFFF)
Drop trigger Trigger name;
go
Create a new change trigger
create
— Trigger identifier
trigger
— The name of the schema to which the DML trigger belongs. The scope of a DML trigger is the schema of the table or view for which the trigger was created. Cannot be specified for DDL or login trigger
–[dbo.]
— Trigger name
Trigger name
On — The table or view on which DML triggers are performed, sometimes referred to as the trigger table or trigger view. You can specify fully qualified names for tables or views as required. Views can only be referenced by INSTEAD OF triggers. DML triggers cannot be defined for local or global temporary tables.
[schema name.] {table name | view name}
–with
Blur the text of the CREATE TRIGGER statement. Using WITH ENCRYPTION prevents triggers from being published as part of SQL Server replication. Cannot specify WITH ENCRYPTION for CLR triggers. (Specifying this option will encrypt the trigger)
–encryption,
— Indicates that the trigger is natively compiled. (Table only)
Triggers on memory optimization tables require this option.
–native_compilation
Ensure that tables referenced by triggers cannot be deleted or changed. (Table only)
Triggers on memory optimized tables require this option, but triggers on traditional tables are not supported.
–schemabinding
–EXECUTE AS
— Specifies the security context used to execute the trigger. The user account that allows you to control the permissions that the SQL Server instance uses to validate any database object referenced by the trigger.
Triggers on memory optimization tables require this option.
–execute as clause
–FOR | AFTER
–AFTER specifies that the DML trigger is fired only when all operations specified in the trigger SQL statement have been successfully executed. All reference cascading operations and constraint checking must also complete successfully before firing this trigger.
— If only the FOR keyword is specified, AFTER is the default.
AFTER triggers cannot be defined for views.
{ for | after }
–{ [DELETE] [,] [INSERT] [,] [UPDATE] }
— Specifies data modification statements that activate DML triggers when they attempt this table or view. You must specify at least one option. Any sequential combination of the above options is allowed in the trigger definition.
For an INSTEAD OF trigger, the DELETE option is not allowed for tables that have a reference relationship specifying the cascading operation ON DELETE. Similarly, the UPDATE option is not allowed for tables that have a reference relationship specifying the cascading operation ON UPDATE.
{ [insert] [,] [update] [,] [delete] }
— Specifies that another trigger of an existing type should be added. WITH APPEND cannot be used WITH INSTEAD OF triggers. If an AFTER trigger is explicitly declared, this clause cannot be used either.
Use WITH APPEND only if FOR is specified FOR backward compatibility (but not INSTEAD OF or AFTER). If EXTERNAL NAME is specified (that is, the trigger is a CLR trigger), WITH APPEND cannot be specified.
–with append
— indicates that triggers should not be executed when the replication agent modifies tables involving triggers.
–not for replication
as
begin
Sql_statement end
go
Example:
Declare database references
use testss;
go
Determine whether the trigger exists
if exists(select * from sysobjects where name=’updatetri’)
drop trigger updatetri;
go
Create a new change trigger
create
— Trigger identifier
trigger
— The name of the schema to which the DML trigger belongs. The scope of a DML trigger is the schema of the table or view for which the trigger was created. Cannot be specified for DDL or login trigger
–[dbo.]
— Trigger name
updatetri
on
The table or view on which DML triggers are performed is sometimes called a trigger table or trigger view. You can specify fully qualified names for tables or views as required. Views can only be referenced by INSTEAD OF triggers. DML triggers cannot be defined for local or global temporary tables.
dbo.test1
–with
Blur the text of the CREATE TRIGGER statement. Using WITH ENCRYPTION prevents triggers from being published as part of SQL Server replication. Cannot specify WITH ENCRYPTION for CLR triggers. (Specifying this option will encrypt the trigger)
–encryption,
— Indicates that the trigger is natively compiled. (Table only)
Triggers on memory optimization tables require this option.
–native_compilation
Ensure that tables referenced by triggers cannot be deleted or changed. (Table only)
Triggers on memory optimized tables require this option, but triggers on traditional tables are not supported.
–schemabinding
–EXECUTE AS
— Specifies the security context used to execute the trigger. The user account that allows you to control the permissions that the SQL Server instance uses to validate any database object referenced by the trigger.
Triggers on memory optimization tables require this option.
–execute as clause
–FOR | AFTER
–AFTER specifies that the DML trigger is fired only when all operations specified in the trigger SQL statement have been successfully executed. All reference cascading operations and constraint checking must also complete successfully before firing this trigger.
— If only the FOR keyword is specified, AFTER is the default.
AFTER triggers cannot be defined for views.
for
–{ [DELETE] [,] [INSERT] [,] [UPDATE] }
— Specifies data modification statements that activate DML triggers when they attempt this table or view. You must specify at least one option. Any sequential combination of the above options is allowed in the trigger definition.
For an INSTEAD OF trigger, the DELETE option is not allowed for tables that have a reference relationship specifying the cascading operation ON DELETE. Similarly, the UPDATE option is not allowed for tables that have a reference relationship specifying the cascading operation ON UPDATE.
update
— Specifies that another trigger of an existing type should be added. WITH APPEND cannot be used WITH INSTEAD OF triggers. If an AFTER trigger is explicitly declared, this clause cannot be used either.
Use WITH APPEND only if FOR is specified FOR backward compatibility (but not INSTEAD OF or AFTER). If EXTERNAL NAME is specified (that is, the trigger is a CLR trigger), WITH APPEND cannot be specified.
–with append
— indicates that triggers should not be executed when the replication agent modifies tables involving triggers.
not for replication
as
begin
declare @oldname nvarchar(100)=null,@newname nvarchar(100)=null;
set @oldname=(select top(1) deleted.name from deleted order by id desc);
set @newname=(select top(1) inserted.name from inserted order by id desc);
if @oldname is not null
begin
update dbo.test2 set name=@newname where id=(select id from dbo.test2 where name=@oldname)
end
end
go
Example results:
Pros and cons of DML AFTER UPDATE triggers
Advantages:
1. Trigger can maintain data faster and more efficiently, saving manpower.
Triggers can be used to enforce data integrity constraints and business rules.
Disadvantages:
1. Poor portability.
2. Occupy server resources and put pressure on the server.
The speed of execution depends on the performance of the database server and the complexity of the trigger code.
4, trigger will make programming source code structure is forced to disrupt, for the program to modify, source code reading difficulties.