JY database trigger operation

USE JY
GO

1. Description:

1. Role: Implementing complex reference integrity and data consistency that primary and foreign keys cannot guarantee

2. Result:
   Triggers and statements that cause them to execute are treated as a transaction if the execution fails.
   Automatic rollback to pre-transaction status

3. Functions:

   1. Enhanced constraints: Constraints that are more complex than check can be implemented to enforce the integrity of the relevant tables in the database
   2. Tracking data changes: Undo or rollback violates referential integrity operations to prevent illegal modification of data
   3. Cascade Running: Cascade modifying tables related to the database, automatically triggering operations related to them
   4. Return custom error information: You can return information, and constraints can only display standard error information of the system.

4. Be careful:

   1.CREATE TRIGGER must be the first statement in batch processing and can only be applied to one table
   2. Like stored procedures, when a trigger triggers, it needs to return results to the calling application; if it does not need to return results, it cannot assign variables in the trigger.
   3. In the same CREATE TRIGGER statement, you can define the same trigger operation for multiple events
   4. Triggers can only be created in the current database, but can refer to external objects in the current database.
   5. If a table's foreign key defines a cascade on DELETE/UPDATE, an INSTEAD OF UPDATE/DELETE trigger cannot be defined on that table.
   6. Use constraints first when constraints can achieve predetermined data integrity
   7. Triggers are not allowed to use statements such as ALTER DATABASE, CREATE DATABASE and DROP DATABASE.
   8. Allow nesting and recursion

2. Create AFTER triggers

• Grammatical Format

CREATE TRIGGER trigger_name
ON {table | view}
[WITH <ENCRPYPTION>]
{
    {FOR | AFTER | INSTEAD OF}
    {[DELETE] [,] [INSERT] [,] [UPDATE]}
    AS
        sql_statement[ , ...]  --Trigger Conditions and Operations
}

• Be careful:

  1.AFTER is executed after all constraint checks have been completed.
  2.INSTEAD OF can solve the problem of foreign key constraints, but it can not solve the problem of whether it is empty, data type or identifying column.
  Execution before execution of constraints

• Example 1:

CREATE TRIGGER tg_updatereader
ON reader
AFTER UPDATE
AS
    BEGIN
        PRINT 'Revised reader list reader Data'
        SELECT reader_name AS 'Pre-update' FROM DELETED
        SELECT reader_name AS 'Updated' FROM INSERTED
    END
GO

UPDATE reader
SET reader_name = 'Hahachen'
WHERE reader_id = 'r0004'
GO

• Example 2:

CREATE TRIGGER tg_insertreader
ON reader
AFTER INSERT
AS
    BEGIN
        RAISERROR('Insertion is not allowed, operation is prohibited', 1, 1)
        ROLLBACK TRANSACTION
    END
GO

INSERT INTO reader
VALUES ('r0010', 'Li Jiajiajia', 'female', 'Department of Foreign Education')
GO

• Example 3:

CREATE TRIGGER tg_deletereader
ON reader
AFTER DELETE
AS
    SELECT reader_id AS 'Deleted user number'
    FROM DELETE
GO

DELETE FROM reader
WHERE reader_id = 'r0009'
GO

• Example 4:

--Insert, delete, update record Cascade update of borrowing records at table time
CREATE TRIGGER setview
ON record
FOR INSERT, UPDATE, DELETE
AS
    UPDATE book SET interview_times = interview_times + 1
        WHERE book_id = (SELECT book_id FROM INSERTED)
    UPDATE book SET interview_times = interview_times - 1
        WHERE book_id = (SELECT book_id FROM DELETED)
    PRINT 'Updated automatically book The number of books borrowed in the table'
GO

3. Create INSTEAD OF trigger

CREATE TRIGGER intg_updatereader
ON reader
INSTEAD OF UPDATE
AS
    PRINT 'Actually, the data has not been modified.'
GO

UPDATE reader SET reader_name = 'Hahachen'
WHERE reader_id = 'r0004'
GO

4. Management triggers

• Update trigger

ALTER TRIGGER tg_updatereader
ON reader
AFTER UPDATE
AS
    IF((SELECT count(*) FROM INSERTED) <> 0)
        PRINT 'Modified reader Table data'
    ELSE
        PRINT 'There is no data to change'
GO

UPDATE reader SET reader_name = 'Michael'
WHERE reader_id = 'r0012'
GO

• Delete trigger

DROP TRIGGER trigger_name[ , ...]

• Enable and Disable Triggers

--Prohibit
ALTER TABLE reader
DISABLE TRIGGER tg_updatereader
--DISABLE TRIGGER tg_updatereader ON reader

--Enable
ALTER TABLE reader
ENABLE TRIGGER tg_updatereader
--ENABLE TRIGGER tg_updatereader ON reader

• View triggers

SELECT *
FROM sysobjects WHERE TYPE = 'TR'