DML AFTER INSERT trigger creation principle
When a trigger is triggered, the system automatically creates a deleted table or INSERTED table in the memory. The created table is read-only and cannot be modified. After the trigger is complete, the deleted table is automatically deleted.
Insert trigger how the insert trigger works: The first step executes the INSERT statement to insert rows into the table. The second step fires the INSERT trigger to insert a backup (copy) of the new row into the system temporary INSERT table. The third trigger checks the new row in the INSERT table to determine whether to roll back or perform other operations.
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 INSERT trigger creation
Syntax: contains parameter definitions
Declare database references
Use database name;
go
— Determines whether a trigger exists and deletes it if it does
If exists(select * from sysobjects where name= FFFF)
Drop trigger Trigger name;
go
Create insert trigger
create
Trigger – Trigger identifier
— 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
[Schema name.] Trigger name – The name of the trigger
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.
On [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.
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
— Determines whether a trigger exists and deletes it if it does
if exists(select * from sysobjects where name=’inserttri’)
drop trigger inserttri;
go
Create insert trigger
create
Trigger – Trigger identifier
–[dbo.] — 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. inserttri — The name of the trigger
On DBO.test1 — The table or view on which DML triggers are performed, 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.
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.
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.
insert
— 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 @names nvarchar(50)=null;
set @names=(select top(1) name from dbo.test1 order by id desc );
if(select @names) is not null
begin
insert into dbo.test2(name) values(@names);
end
end
go
Example results:
Pros and cons of DML AFTER INSERT triggers
Advantages:
1. More stringent constraint checking can be implemented when adding a constraint.
2. Complex business logic association between tables can be realized.
3, can realize data insertion automatically add log table records.
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.