How the INSTEAD OF trigger works
INSTEAD OF performing INSERT, UPDATE, or DELETE operations on a table, perform only the trigger itself. INSTEAD OF performing INSERT, UPDATE, or DELETE operations on a table, perform the trigger itself. Let the trigger check to see if the action is correct, and do the corresponding action only if it is. Therefore, the INSTEAD OF trigger’s action precedes the constraint processing OF the table. INSTEAD OF triggers can be defined on a table or on a view. The inserted and deleted tables passed to the INSTEAD OF trigger defined for the table follow the same rules as the inserted and deleted tables passed to the AFTER trigger. The format OF the inserted and deleted tables is the same as that OF the tables on which the INSTEAD OF trigger is defined. Each column in the inserted and deleted tables maps directly to the columns in the base table.
Here are the rules for when an INSERT or UPDATE statement referencing a table with an INSTEAD OF trigger must supply column values, as well as when referencing a table without an INSTEAD OF trigger:
You cannot specify values for computed columns or columns with timestamp data type.
You cannot specify a value for a column that has the IDENTITY attribute unless the table’s IDENTITY_INSERT is ON. When IDENTITY_INSERT is ON, the INSERT statement must provide a value.
INSERT statements must provide values for all NOT NULL columns that have no DEFAULT constraint.
Any column or timestamp column value other than identity is optional for computation. Any columns that allow NULL values or columns that have NOT NULL defined by DEFAULT.
When an INSERT, UPDATE, or DELETE statement references a view with an INSTEAD OF trigger, the database engine invokes that trigger INSTEAD OF taking any direct action on any table. Even if the format of the information generated for the view in the inserted and deleted tables is different from the format of the data in the base table, the trigger must use the information in the inserted and deleted tables to generate any statements required to implement the requested operation in the base table.
INSTEAD OF trigger creation
Grammar:
Declare the database application.
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
Execute create trigger
create
— Trigger keyword
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
[architecture.] {table name | view name}
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.
[Schema name.] Trigger 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]
— Specifies that DML triggers are executed instead of triggering SQL statements and, therefore, take precedence over operations that trigger statements. You cannot specify INSTEAD OF for DDL or login triggers.
Each INSERT, UPDATE, or DELETE statement can define up to one INSTEAD OF trigger for a table or view. However, you can define views for multiple views that have their own INSTEAD OF triggers.
Triggers cannot be used for updatable views using The WITH CHECK OPTION. If you add an INSTEAD OF trigger to an updatable view WITH CHECK OPTION specified, SQL Server will raise an error. To define an INSTEAD OF trigger, the user must remove this option with ALTER VIEW.
instead of
— 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 the database application.
use testss;
go
— Determines whether a trigger exists and deletes it if it does.
if exists(select * from sysobjects where name=’insteadoftri’)
drop trigger insteadoftri;
go
Execute create trigger
create
— Trigger keyword
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
dbo.insteadoftri
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]
— Specifies that DML triggers are executed instead of triggering SQL statements and, therefore, take precedence over operations that trigger statements. You cannot specify INSTEAD OF for DDL or login triggers.
Each INSERT, UPDATE, or DELETE statement can define up to one INSTEAD OF trigger for a table or view. However, you can define views for multiple views that have their own INSTEAD OF triggers.
Triggers cannot be used for updatable views using The WITH CHECK OPTION. If you add an INSTEAD OF trigger to an updatable view WITH CHECK OPTION specified, SQL Server will raise an error. To define an INSTEAD OF trigger, the user must remove this option with ALTER VIEW.
instead of
— 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
if (select count(1) from inserted) = 1 and (select count(1) from deleted) = 0
begin
print(‘insert’);
insert into dbo.test1(name,sex,age,classid,height,xml1,xml2)
select top 1 name,sex,age,classid,height,xml1,xml2 from inserted order by id desc;
end
else if (select count(1) from deleted) = 1 and (select count(1) from inserted) = 0
begin
print(‘delete’);
delete from dbo.test1 where id=(select top 1 id from deleted order by id desc);
end
else
begin
print(‘update’);
update dbo.test1 set name=inserted.name,sex=inserted.sex,age=inserted.age,classid=inserted.classid,height=inserted.height, xml1=inserted.xml1,xml2=inserted.xml2 from inserted where dbo.test1.id=(select top 1 id from deleted order by id desc);
end
end
go
Example results:
Pros and cons OF an INSTEAD OF trigger
Advantages:
1, can achieve more stringent checks and verification before data operation.
2. The operation can be defined on a table or a view. Only one operation can be defined.
3. Specify that DML triggers are executed instead of triggering SQL statements and, therefore, take precedence over operations that trigger statements.
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.