SQL Server trigger and SQL Server trigger

SQL Server trigger and SQL Server trigger
SQL Server triggers

A trigger is a special type of stored procedure, which is different from the stored procedure we introduced earlier. A trigger is triggered by an event and automatically called for execution. The stored procedure can be called by the name of the stored procedure.

Ø what is a trigger?

A special stored procedure that is automatically executed when a trigger inserts, updates, or deletes a table. Triggers are generally used for constraints with more complex check constraints. The difference between a trigger and a common stored procedure is that a trigger is used to operate a table. For example, update, insert, and delete operations, the system automatically calls the trigger for executing the table. In SQL Server 2005, triggers can be divided into two types: DML triggers and DDL triggers. DDL triggers affect the execution of multiple data definition language statements, including create, alter, and drop statements.

 

DML triggers are divided:

1. after trigger (triggered later)

A. insert trigger

B. update trigger

C. delete trigger

 

2. instead of trigger (previously triggered)

 

The after trigger requires that the trigger be triggered only after an insert, update, or delete operation is executed and can only be defined on the table. The instead of trigger does not execute the defined operations (insert, update, delete) but only executes the trigger itself. You can either define an instead of trigger on a table or a view.

 

The trigger has two special tables: The Insert table and the delete table ). These two tables are logical tables and virtual tables. Two tables created by the system in the memory are not stored in the database. Both tables are read-only and can only read data but cannot modify data. The results of these two tables are always the same as the structure of the table applied by the modified trigger. After the trigger is complete, the two tables will be deleted. The data in the Inserted Table is the Inserted or modified data, and the data in the deleted table is the data before or after the update.

 

Table operations	Inserted logical table	Deleted logical table
Insert)	Store added records	None
Delete)	None	Store deleted records
Update)	Store updated records	Store records before update

When updating data, you delete the table record and add a record. In this way, the inserted and deleted tables have updated data records. Note: The trigger itself is a transaction, so you can perform some special checks on the modified data in the trigger. If not, you can use transaction rollback to cancel the operation.

 

Create a trigger

Syntax

create trigger tgr_name
on table_name
with encrypion-Encryption trigger
    for update ...
as
    Transact-SQL

    # Create insert trigger

--Create an insert insert type trigger
if (object_id ('tgr_classes_insert', 'tr') is not null)
    drop trigger tgr_classes_insert
go
create trigger tgr_classes_insert
on classes
    for insert-insert trigger
as
    --Define variables
    declare @id int, @name varchar (20), @temp int;
    --Query the inserted record information in the inserted table
    select @id = id, @name = name from inserted;
    set @name = @name + convert (varchar, @id);
    set @temp = @id / 2;
    insert into student values (@name, 18 + @id, @temp, @id);
    print 'Add student successfully! ';
go
--Insert data
insert into classes values ('5 班', getDate ());
--Query data
select * from classes;
select * from student order by id;
     The insert trigger will add a newly inserted record to the inserted table.

 

    # Create delete trigger

--delete delete type trigger
if (object_id ('tgr_classes_delete', 'TR') is not null)
    drop trigger tgr_classes_delete
go
create trigger tgr_classes_delete
on classes
    for delete-delete trigger
as
    print 'Backing up data ...';
    if (object_id ('classesBackup', 'U') is not null)
        --ClassBackup exists, insert data directly
        insert into classesBackup select name, createDate from deleted;
    else
        --- There is no classBackup created and then inserted
        select * into classesBackup from deleted;
    print 'Backup data succeeded! ';
go
-
-Do not show the number of affected rows
--set nocount on;
delete classes where name = '5 classes';
--Query data
select * from classes;
select * from classesBackup;
   The delete trigger will save the data just deleted in the deleted table when deleting the data.

 

    # Create update type trigger

--update update type trigger
if (object_id ('tgr_classes_update', 'TR') is not null)
    drop trigger tgr_classes_update
go
create trigger tgr_classes_update
on classes
    for update
as
    declare @oldName varchar (20), @newName varchar (20);
    --Data before update
    select @oldName = name from deleted;
    if (exists (select * from student where name like '%' + @oldName + '%'))
        begin
            --- Updated data
            select @newName = name from inserted;
            update student set name = replace (name, @oldName, @newName) where name like '%' + @oldName + '%';
            print 'Cascading modified data successfully! ';
        end
    else
        print 'No need to modify the student table! ';
go
--Query data
select * from student order by id;
select * from classes;
update classes set name = 'Five shifts' where name = '5 shifts';
     The update trigger will save the data before the update in the deleted table after updating the data, and save the updated data in the inserted table.

 

    # update Update column-level triggers

if (object_id ('tgr_classes_update_column', 'TR') is not null)
    drop trigger tgr_classes_update_column
go
create trigger tgr_classes_update_column
on classes
    for update
as
    --Column-level trigger: whether the class creation time was updated
    if (update (createDate))
    begin
        raisError ('System prompt: Class creation time cannot be modified!', 16, 11);
        rollback tran;
    end
go
--test
select * from student order by id;
select * from classes;
update classes set createDate = getDate () where id = 3;
update classes set name = 'four shifts' where id = 7;
     Update column-level triggers can use update to determine whether to update column records;

 

    # instead of type triggers

       instead of trigger means that it does not perform its defined operation (insert, update, delete) but only executes the contents of the trigger itself.

       Create syntax

create trigger tgr_name
on table_name
with encryption
    instead of update ...
as
    T-SQL
   

      # Create instead of triggers

if (object_id ('tgr_classes_inteadOf', 'TR') is not null)
    drop trigger tgr_classes_inteadOf
go
create trigger tgr_classes_inteadOf
on classes
    instead of delete / *, update, insert * /
as
    declare @id int, @name varchar (20);
    --- Query deleted information, disease assignment
    select @id = id, @name = name from deleted;
    print 'id:' + convert (varchar, @id) + ', name:' + @name;
    -Delete student's information first
    delete student where cid = @id;
    -Then delete the class information
    delete classes where id = @id;
    print 'Remove information from [id:' + convert (varchar, @id) + ', name:' + @name + '] successfully! ';
go
--test
select * from student order by id;
select * from classes;
delete classes where id = 7;
   

      # Display custom message raiseerror

if (object_id ('tgr_message', 'TR') is not null)
    drop trigger tgr_message
go
create trigger tgr_message
on student
    after insert, update
as raisError ('tgr_message trigger fired', 16, 10);
go
--test
insert into student values ('lily', 22, 1, 7);
update student set sex = 0 where name = 'lucy';
select * from student order by id;

    # Modify trigger

alter trigger tgr_message
on student
after delete
as raisError ('tgr_message trigger fired', 16, 10);
go
--test
delete from student where name = 'lucy';

    # Enable and disable triggers

-Disable trigger
disable trigger tgr_message on student;
--Enable trigger
enable trigger tgr_message on student;

    # Query the created trigger information

--Query existing triggers
select * from sys.triggers;
select * from sys.objects where type = 'TR';

--View trigger trigger events
select te. * from sys.trigger_events te join sys.triggers t
on t.object_id = te.object_id
where t.parent_class = 0 and t.name = 'tgr_valid_data';

--View create trigger statement
exec sp_helptext 'tgr_message';

    # Example, verify insert data

if ((object_id ('tgr_valid_data', 'TR') is not null))
    drop trigger tgr_valid_data
go
create trigger tgr_valid_data
on student
after insert
as
    declare @age int,
            @name varchar (20);
    select @name = s.name, @age = s.age from inserted s;
    if (@age <18)
    begin
        raisError ('There is a problem with age when inserting new data', 16, 1);
        rollback tran;
    end
go
--test
insert into student values ('forest', 2, 0, 7);
insert into student values ('forest', 22, 0, 7);
select * from student order by id;

    # Example, operation log

if (object_id ('log', 'U') is not null)
    drop table log
go
create table log (
    id int identity (1, 1) primary key,
    action varchar (20),
    createDate datetime default getDate ()
)
go
if (exists (select * from sys.objects where name = 'tgr_student_log'))
    drop trigger tgr_student_log
go
create trigger tgr_student_log
on student
after insert, update, delete
as
    if ((exists (select 1 from inserted)) and (exists (select 1 from deleted)))
    begin
        insert into log (action) values ('updated');
    end
    else if (exists (select 1 from inserted) and not exists (select 1 from deleted))
    begin
        insert into log (action) values ('inserted');
    end
    else if (not exists (select 1 from inserted) and exists (select 1 from deleted))
    begin
        insert into log (action) values ('deleted');
    end
go
--test
insert into student values ('king', 22, 1, 7);
update student set sex = 0 where name = 'king';
delete student where name = 'king';
select * from log;
select * from student order by id;


This article comes from: http://www.cnblogs.com/hoojo/archive/2011/07/20/2111316.html


How does the SQL Server 2005 trigger work and when does it fire?
A trigger is a statement. Consists of two parts. One part is the trigger action, and the other is the action performed after the trigger.
When you operate on the related table, this action may touch the statement written by the trigger, and then perform the next action.
Example: INSERT trigger
CREAT TRIGGER plus ON dbo.A means create a trigger named plus in table A
FOR INSERT action is to insert data
AS
BEGIN TRANSACTION Perform check
UPDATE dbo.B The object to perform the action is to update the B table
SET aa = bb + cc updates the value of column aa in table B to the sum of column bb and column cc
WHERE cc IN (SELECT cc FROM INSERTED) where inserted refers to the new value being inserted
COMMIT TRANSACTION
GO
The meaning of the entire statement is: I create a trigger named plus in table A. If I insert a value into table A, I start to perform a check and update column aa in table B so that the value of that column It is equal to bb + cc in table A. After the cc column is inserted into the value, this action is triggered and requires mandatory execution.
 
Problems with SQL Server triggers

careate trigger trigger_name on B
for insert
as
declare @a varchar (20),
seelct @ a = name from inserted // Note: inserted and deleted are special tables for SQL Server triggers
if not exists (select name from A where name = @ a)
begin
rollback transaction
else
commit
end
go