Using After Delete Trigger in F#

Introduction

In this article I explained how to delete records in one table and after using an "after delete" trigger on the table and deleting records they are saved in another table. In a previous articles you can learn about the After Insert Trigger and After Update Trigger.

After Delete Trigger

A delete trigger is a kind of Stored Procedure that executes itself when a delete statement deletes data from a table on which the trigger is configured. When a delete trigger is fired the deleted rows from the affected table are placed in a logical deleted table. The logical deleted table is kind of a table that contains the copy of deleted rows from the affected table.

Features of After Delete Trigger

• The deleted table is always in the cache.
• Space is allocated from the memory to create the deleted table.
• A trigger that is defined for the delete action will not executes itself if the truncate statement is used because the truncated table is not logged and the delete trigger is not fired.

Why to use Triggers

• Provide auditing
• Prevent invalid transactions
• Maintain Synchronous table replicates
• Modify table data when DML statements are issued against views
• Automatically generate derived column values

After Triggers

An "after trigger" fired by DML statements can be defined only on tables, not on views. "After triggers" are executed after an insert, update or delete on a specified table.

• After Insert Trigger
• After Update Trigger
• After Delete Trigger

Enable or Disable the Trigger

You can enable or disable the trigger in database syntax:

alter table table_name enable trigger trigger_name

 

alter table table_name disable trigger trigger_name

 

For "After Delete" Trigger

Create Table-1

Create Database DemoTriggers

use DemoTriggers

Create table FirstTable

(

CustID int,

CustName varchar(max),

CustAddress nvarchar(max),

PaidAmmount decimal

)

 

Insert values into table fields.

 

insert into FirstTable values(101,'pankaj','vinod nagar',1200)

insert into FirstTable values(102,'Nimit Joshi','New Delhi',6000)

insert into FirstTable values(103,'Amit','West Vinod Nagar',800)

 

 

Create Table-2

Create table SecondTable

(

CustID int,

CustName varchar(max),

CustAddress nvarchar(max),

PaidAmmount decimal

)

 

There are no records in the Second Table until the "after delete" trigger is fired.

 

Create Procedure for Delete

Create proc [dbo].[DeleteData]

@cid int

as

begin

delete from FirstTable where CustID=@cid

end

 

Create Trigger For Delete

 

Create trigger [dbo].[trgDelete] on [dbo].[FirstTable]

for delete

as 

declare @cid int;

declare @cname varchar(max);

declare @cadd nvarchar(max);

declare @pammount decimal;

select @cid=i.CustID from deleted i;

select @cname=i.CustName from deleted i;

select @cadd=i.CustAddress from deleted i;

select @pammount=i.PaidAmmount from deleted i;

insert into SecondTable values(@cid,@cname,@cadd,@pammount)

 

 

The create Trigger statement is used to create a trigger and an "on" clause specifies the table name on which the trigger is to be attached.  In this trigger the deleted records are accessed from the logical table, the "logical deleted table" and inserted into the second table.

Now  I want to show the effect of an "after insert" trigger in a database. Just use the following procedure.

Step 1:

Open Visual Studio then select "Create New Project" --> "F# Console Application".

Step 2:

Now go to the Solution Explorer on the right side of the application. Select "References" and right-click on it and select "Add references".

Step 3:

After selecting "Add References", in the framework template you need to select "System.Windows.Forms", "System.Drawing", "System.Xml" and "System.Data" while holding down the Ctrl key and click on "Ok." 

Step 4:

Write the following code in the F# editor:

//Importing Namespaces

open System

open System.Windows.Forms

open System.Drawing

open System.Data.SqlClient

open System.Data

// Creating Form & User Controls

let MyForm = new Form(Height =400, Width = 500)

//   Labels

let lblId = new Label(Top =20 ,Left=0, Height =30, Width = 120)

let lblafdelete = new Label(Top =100 ,Left=30, Height =20, Width = 80)

let lblbafdelete = new Label(Top =100 , Left=420, Height =20, Width = 120)

//   TextBoxes

let txtID = new TextBox(Top =20, Left =120,BorderStyle=BorderStyle.FixedSingle)

lblId.Text <- " Enter Customer ID:"

lblafdelete.Text <- "After Delete"

lblbafdelete.Text <- "Before/After Delete"

let btnTable1 = new Button(Top =120, Left =30, Height =20, Width =90)

let btnTable2 = new Button(Top =120, Left =420, Height =20, Width =90)

let btnsearch = new Button(Top =20, Left =250, Height =20, Width =80)

btnTable1.Text <- "FirstTable"

btnTable2.Text <- "SecondTable"

btnsearch.Text <- "Delete"

//Adding Controls to Form

MyForm.Controls.Add(lblId)

MyForm.Controls.Add(lblafdelete)

MyForm.Controls.Add(lblbafdelete)

MyForm.Controls.Add(txtID)

MyForm.Controls.Add(btnTable1)

MyForm.Controls.Add(btnTable2)

MyForm.Controls.Add(btnsearch)

//Database Connectivity

let constr = @"Data Source=MCNDESKTOP34;Initial Catalog=DemoTriggers; User Id=sa; Password=mcn@123"

let con = new SqlConnection(constr)

let com1 = new SqlCommand()

btnsearch.Click.Add(fun _->

 con.Close()

 com1.Connection <- con

 con.Open()

 com1.CommandType <- CommandType.StoredProcedure

 com1.CommandText <- "DeleteData" //DeleteData is a stored procedure

 com1.Parameters.AddWithValue("@cid",txtID.Text) |> ignore

 com1.ExecuteNonQuery()|> ignore

 con.Close()

 MessageBox.Show("After Delete Trigger Fired on Firsttable")|>ignore

 txtID.Clear()

 txtID.Focus() |>ignore

)

btnTable1.Click.Add(fun _ ->

let adapter = new SqlDataAdapter("select * from FirstTable",con)

let ds = new DataSet()

adapter.Fill(ds) |>ignore

let gridview = new DataGridView(Top=150,Width=420, Height=150)

MyForm.Controls.Add(gridview)

gridview.DataSource <- ds.Tables.[0]

)

btnTable2.Click.Add(fun _ ->

let adapter = new SqlDataAdapter("select * from SecondTable",con)

let dt = new DataTable()

adapter.Fill(dt) |>ignore

let gridview = new DataGridView(Top=150,Left=420,Width=400,Height=150)

MyForm.Controls.Add(gridview)

gridview.DataSource <- dt

)

Application.Run(MyForm)

 

Step 5:

Debug the application by pressing F5 to execute the Windows Forms application. After debugging the application you will get a Windows Forms application as in the following figure.

Step 6:

Click on "First Table" to see the records of the first table.

Step 7:

Now click on "Second Table" to see the deleted records of the First Table.

Step 8:

Now enter the customer id of the record you want to remove from the FirstTable.

Step 9:

After the Delete Trigger is fired on the First Table.

Step 10:

Records are shown in a DataGridView. The table shows the after deleted records and before deleted records in the Second Table.

Deleted rows are inserted into the second table.

Summary

This article has shown how to create an "after delete" trigger on a table. First we explained the trigger and how to use the "after delete" trigger and why to use the triggers. You can see the After Insert Trigger and After Update Trigger in my previous articles.