What is a trigger?

trigger is a method that SQL server provides programmers and data analysts to ensure data integrity. Its execution is triggered not by program calls or by hand, but by events. such as When a table is operated on (insert, delete, update), it is activated to execute.

Combining with an example, the following is a simple illustration: if you want to delete a news class in the "Brisket News System", it is not possible to enter news class directly on the form. Reason 1: Your database relational table sets primary and foreign key connections, deletes a connection class, and the relationship will be broken. If you want to remove the primary key and foreign key, you can solve the problem, but the dependencies between data structures can not be expressed. Reason 2: There are news comments in your news category, news comments are deleted, whether news comments are deleted or not is also a question. When we use events to activate triggers, the triggers you create can be implemented step by step in the order of first finding the category to be deleted, then deleting the data and other information of the corresponding comment table in the category, and finally deleting the class. This not only keeps the relational dependency of primary and foreign keys, but also deletes several tables that meet the requirements at the same time.

sqlserver 2014 How to Establish Triggers

Generally speaking, find the list of triggers you want to set up in the database, then mail triggers choose new triggers, but SQL Server 2014 does not support it.

Then you need to set up the trigger manually. First of all, select the mouse you want to create a trigger table, in the toolbar stand-alone new queries

Then enter the code for the new trigger in the following format. If the reader can not understand the meaning of the comment, you can refer to Teacher Niu Bing's Video Lesson 11 to write the code.

--Author(Author):
--Create Date(Time):
--Description(Name):
CREATE TRIGGER [Trigger Name]                  --Create the name of the trigger
    on [Table name]                              --Select the table to set up the trigger
    AFTER INSERT/DELETE/UPDATE              --Trigger condition insertion/delete/Update (pick one)
AS                                          --Accompany
BEGIN                                       --Start execution
    SQL Statement ( select * from deleted)         --Fill in the trigger to execute here SQL Statement (for example, find what has just been deleted)
    ...                                     --"..."Representation can be multiple lines
END                                         --End
GO                                          --

In the sirloin system, the AFTER in the figure above should be changed to INSTEAD OF.

Author Generates Code Reference

USE [newssystem]
GO
/****** Object:  Trigger [dbo].[trigCategoryDelete]    Script Date: 2018/10/14 20:28:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Author(Author):
--Create Date(Time):
--Description(Name):
ALTER TRIGGER [dbo].[trigCategoryDelete]                  --Create the name of the trigger
    on [dbo].[category]                              --Select the table to set up the trigger
    Instead of DELETE              --Trigger condition insertion/delete/Update (pick one)
AS                                          --Accompany
BEGIN                                       --Start execution
    declare @caId int
	select @caId=id from deleted

	delete comment where newsId in(select newsId from news where caId=@caId)
	delete news where caId=@caId
	delete category where id=@caId

END                                         --End

After you finish writing and make sure that you write the following code in the interface of your program, you can achieve the function!

        protected void Button1_Click(object sender, EventArgs e)
        {
            string caName = TextBox1.Text;
            string id = TextBox2.Text;
            Category ca = new Category(id, caName);
            bool b = new CategoryDao().Delete(id);
            Response.Write(b);
            GridView1.DataSource = new CategoryDao().SelectAll();
            GridView1.DataBind();
        }