Department Table _ Delete:
Use [Test]go/** * * * Object:trigger [dbo]. [Department _delete] Script DATE:2015/8/31 16:41:46 * * * * **/SET ansi_nulls ongoset quoted_identifier Ongo--=============================================--Author: <author,ammy,name>--Create Date: <create date, , >--Description: <description, shows how many records were modified by the action trigger, >--because each operation type is activated once for one trigger, the operation may update or delete 10 records .--The inserted and deleted tables will have 10 records, using the system variable @ @ROWCOUNT to know the number of records changed in the last Operation-- =============================================CREATE TRIGGER [dbo]. [Department _delete] on [Test]. [dbo]. [Department Table] After Deleteasbegin PRINT'you have deleted this'+cast (@ @ROWCOUNT as VARCHAR) +'article Records'Endgo
Department Table _ Insert:
Use [Test]go/** * * * Object:trigger [dbo]. [Department _insert] Script DATE:2015/8/31 16:42:29 * * * * **/SET ansi_nulls ongoset quoted_identifier Ongo--=============================================--Author: <author,ammy,name>--Create Date: <create date, -/ ,/ +, >--Description: <description, when the departmental table is inserted, you are prompted to add a product and insert a record into the log table >--====================================== =======CREATE TRIGGER [dbo]. [Department _insert] on [dbo]. [Department Table] After Insertasbegin DECLARE @ Event Department name NVARCHAR ( -) SELECT @ Event Department name=department name from inserted IF (SELECT LEN (phone number) from inserted)<> OneBEGIN PRINT'number of digits must be 11 digits'ROLLBACK TRANSACTION END ELSE BEGIN Print'and added a department.'INSERT into test.dbo. Log table (event name, event person, event time) VALUES ('To Add a new department:'[Email protected] Event department name,'Admin', GETDATE ()); --Setting this trigger to a nested type of true if a trigger is established on the dbo. Log table triggers the activation of dbo. Log table _insert trigger Endendgo
Department Table _ Update:
Use [Test]go/** * * * Object:trigger [dbo]. [Departmental Table _ can only modify department number] Script DATE:2015/8/31 16:43:17 * * * * **/SET ansi_nulls ongoset quoted_identifier Ongo--=============================================--Author: <author,ammy,name>--Create Date: <create date, -- ,- -, >--Description: <description, use Update (field name) to complete a trigger that can only modify a field >--=========================================== ==CREATE TRIGGER [dbo]. [Departmental Table _ can only modify department number] on [dbo]. [Department Table] Instead of Updateasbegin SET NOCOUNT on; IF UPDATE (department number) BEGIN DECLARE @ Department number NVARCHAR ( -), @ Department name NVARCHAR ( -), @ Department Responsibility Nvarchar ( -) SET @ Department number=(SELECT department number from inserted) SET @ Department Name=(SELECT department name from inserted) SET @ departmental Responsibilities=(SELECT department responsibility from inserted) UPDATE Department Table SET Department number=@ Department Number WHERE department name[email protected] Department name--and Department number [email protected] Department number and @ Department responsibility =@ Department Responsibilities--This is not a good idea because only the action-only statements will work with this modification. END ELSE BEGIN PRINT'only department number can be modified'RAISERROR ('no modifications are allowed except for the Department number field', -,5) --Print output custom information, but you can only run SQL statements in the Query Editor pane to see these customized information .--other front-end applications do not display these customized information Endendgo
Log table triggers:
Use [Test]go /* * * * Object: Trigger [dbo]. [Log table _insert] Script DATE:2015/8/31 16:44:11 * * * * *SET ansi_nulls ongoset quoted_identifier Ongo--========= ====================================--Author: <author,,name>--Create Date: <create date,,>-- Description: <description,,>--=============================================CREATE TRIGGER [dbo].[ Log table _insert]on [test]. [dbo]. [Log Table] After Insertas BEGIN PRINT' database again, there's a record change 'END GO
Database Trigger _trigger