Using After Update Trigger in F#

Introduction

In this article I have explained how to update the records in one table then after using an "after update" trigger on the table the records are automatically updated in another table. In the previous article you can learn about the After Insert Trigger.

Trigger

Triggers are database objects that are automatically executed when a DDL or DML command statement is executed. Triggers are used to evaluate the data before or after data modification using DDL/DML statements. Triggers are an action that is performed implicitly.

Why we use Triggers?

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

After Triggers

"After" triggers are fired by DML statements and can only be defined 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 Update Trigger

Create Table-1

Create Database DemoTriggers

use DemoTriggers

Create table FirstTable

(

CustID int,

CustName varchar(max),

CustAddress nvarchar(max),

PaidAmmount decimal

)

Create Table-2

Create table SecondTable

(

CustID int,

CustName varchar(max),

CustAddress nvarchar(max),

PaidAmmount decimal

)

Create Procedure for Updates

Create proc [dbo].[UpdateData]

@cid int,

@cname varchar(max),

@cadd nvarchar(max),

@pammount decimal

begin

update FirstTable set CustID=@cid,CustName=@cname,CustAddress=@cadd,PaidAmmount=@pammount

where CustId=@cid

end

Create Trigger For Update

Create TRIGGER [dbo].[trgAfterUpdate] ON [dbo].[FirstTable]

FOR UPDATE

declare @cid int;

declare @cname varchar(max);

declare @cadd nvarchar(max);

declare @pammount decimal;

select @cid=i.CustID from inserted i;

select @cname=i.CustName from inserted i;

select @cadd=i.CustAddress from inserted i;

select @pammount=i.PaidAmmount from inserted i;

if update(CustID)

if update(CustName)

if update(CustAddress)

if update(PaidAmmount)

insert into SecondTable(CustID,CustName,CustAddress,PaidAmmount)

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 the trigger body the table named "inserted" has been used, it is a logical table and contains the row that has been inserted. The "After Update" Trigger is created in the updated record that is inserted into the Second Table. There is no logical table updated such like Inserted table. We can obtain the updated value of a field from from the update(Column_Name) function.

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 the references and right-click on it and select "Add references".

do right-click on Refrences

Step 3:

After selecting "Add References", in the framwork 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 lblName = new Label(Top =50 ,Left=0, Height =30, Width = 120)

let lblAddress = new Label(Top =80 ,Left=0, Height =30, Width = 120)

let lblPammount = new Label(Top =110 ,Left=0, Height =30, Width = 120)

let lblafupdate = new Label(Top =190 ,Left=30, Height =20, Width = 80)

let lblbafupdate = new Label(Top =190 , Left=250, Height =20, Width = 120)

// TextBoxes

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

let txtName = new TextBox(Top =50, Left =120,BorderStyle=BorderStyle.FixedSingle)

let txtAddress = new TextBox(Top =80, Left =120,BorderStyle=BorderStyle.FixedSingle)

let txtPamount = new TextBox(Top =110,Left=120,BorderStyle=BorderStyle.FixedSingle)

let txtdeleteid = new TextBox(Top =300,Left=80,BorderStyle=BorderStyle.FixedSingle)

lblId.Text <- " Enter Customer ID:"

lblName.Text <- "Customer Name :"

lblAddress.Text <- "Customer Address :"

lblPammount.Text <- "Paid Ammount:"

lblafupdate.Text <- "After Update"

lblbafupdate.Text <- "Before/After Update"

let btnSave = new Button(Top =150, Left =80, Height =20, Width =60)

let btnTable1 = new Button(Top =210, Left =30, Height =20, Width =60)

let btnTable2 = new Button(Top =210, Left =250, Height =20, Width =60)

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

btnSave.Text <- "Update"

btnTable1.Text <- "TABLE-1"

btnTable2.Text <- "TABLE-2"

btnsearch.Text <- "Search"

//Adding Controls to Form

MyForm.Controls.Add(lblId)

MyForm.Controls.Add(lblName)

MyForm.Controls.Add(lblAddress)

MyForm.Controls.Add(lblPammount)

MyForm.Controls.Add(lblafupdate)

MyForm.Controls.Add(lblbafupdate)

MyForm.Controls.Add(txtID)

MyForm.Controls.Add(txtName)

MyForm.Controls.Add(txtAddress)

MyForm.Controls.Add(txtPamount)

MyForm.Controls.Add(btnSave)

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.CommandText <- " select * from FirstTable where CustID = "+txtID.Text+" "

let dr = com1.ExecuteReader()

while dr.Read() do

txtID.Text <- dr.Item(0).ToString()

txtName.Text <- dr.Item(1).ToString()

txtAddress.Text <- dr.Item(2).ToString()

txtPamount.Text <- dr.Item(3).ToString())|>ignore

//Update record into DataBase using Stored procedure

btnSave.Click.Add(fun _->

con.Close()

com1.Connection <- con

con.Open()

com1.CommandType <- CommandType.StoredProcedure

com1.CommandText <- "UpdateData" //Updatedata is a stored procedure

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

com1.Parameters.AddWithValue("@cname",txtName.Text ) |> ignore

com1.Parameters.AddWithValue("@cadd",txtAddress.Text) |> ignore

com1.Parameters.AddWithValue("@pammount",txtPamount.Text) |> ignore

com1.ExecuteNonQuery()|> ignore

con.Close()

MessageBox.Show("Records updated in both tables")|>ignore

txtID.Clear()

txtName.Clear()

txtAddress.Clear()

txtPamount.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=250,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=250,Left=280,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:

Enter the customer id in which we can find the relevant information from the database.

Step 7:

Step 8:

Step 9:

When fetching the records from the database in DataGridView, this figure shows the updated records in the database. Just click on Table-1.

Step 10:

Records are shown in a DataGridView. This table shows the value before the update and after the update in Table-2. Just click on Table-2.

Summary

In this article you saw how to create an "after update" trigger on a table. First we explained the trigger and how to use the "after update" trigger and why we use the triggers. You can see the After Insert Trigger in my previous article.