Click here to Skip to main content
12,552,181 members (56,830 online)
Rate this:
Please Sign up or sign in to vote.
See more: C# SQL-Server C#4.0
How to use a delete trigger on certain update from my cs file.
Posted 6-Mar-12 20:06pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

I don't think its possible. And I'm not sure why you would want to call a trigger from your code. Triggers automatically initiate during update, insert or delete. So your trigger should automatically execute after your update.
AshishMishrag 12-Mar-12 3:03am
thankyou for ur response.
Rate this: bad
Please Sign up or sign in to vote.

Solution 5

SQL trigger can be created programmatically in C#. The trigger will run each time an insert, update or delete occurs on table(s) in the database. SQL Server provides two special read-only, memory resident tables, inserted and deleted, to test the effects of certain data modifications and to set conditions for trigger actions.

In our Insert trigger, we use the inserted table, which stores copies of the affected rows during INSERT, UPDATE and DELETE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. During a delete transaction, rows are removed both from the inserted and the trigger table. So by writing a where clause that specifies the primary key field matches in our target table and the inserted table, we are able to update the specific record in our target table.

The Insert trigger will look like -
CREATE TRIGGER trg_insert_tablename
ON tablename FOR INSERT AS
UPDATE tablename SET DateCreated=GetDate(),
WHERE (tablename.primarykeyfield1 =
(select primarykeyfield1 from inserted)
AND ...( tablename primarykeyfieldn =
(select primarykeyfieldn from inserted) )
The Update trigger will look identical to the Insert trigger, except that DateCreated will not be updated, only DateModified.

First create a windows application in Visual Studio. In the default Form.cs page add a button(named cmdCreateTriggers). Add an OnClick event for this button and point it to cmdCreateTriggers_click() fucntion. In this function we will write the code for the SQL trigger.
private void cmdCreateTriggers_click
(object sender, System.EventArgs e)
string sqlInsert;
string sqlUpdate;
string sqlDropTriggers;
string sqlWhere;
string tableName;
string sqlAllTables;
string sqlPrimaryKeys;
string strConn;
//Using INFORMATION_SCHEMA to view the metadata of a SQL Server database
//TABLE view lists all the tables in a particular database
//KEY_COLUMN_USAGE table lists all the primary keys that exist in a particular database

//Filter out any tables that Microsoft ships with versions of SQL Server
sqlAllTables = "SELECT Table_name from INFORMATION_SCHEMA.TABLES " +
               "WHERE Table_type = 'BASE TABLE' " +
//Set value of myconnectionstring to your connection string
string connectionString = "myconnectionstring";
SqlConnection con = new SqlConnection(connectionString);
//Declare a DataSet
DataSet dsTrigger = new DataSet();
DataTable dtTables = new DataTable();
//We have a list of tables that need triggers, let's loop through those tables and create the CREATE TRIGGER statements

foreach (DataRow childRow in
      //Loop through the rows, accessing the column_name property of the childRow object, to build the complete WHERE clause for the trigger.
//Once the complete CREATE TRIGGER statement is built, we simply output the SQL statements to a .sql file
TextWriter outSql = new StreamWriter("trigger.sql");
AshishMishrag 21-Jun-13 2:31am
thanks Arunabh it worked for me.
Canny Brisk 25-Feb-14 4:28am

Rate this: bad
Please Sign up or sign in to vote.

Solution 2

AshishMishrag 12-Mar-12 3:03am
thankyou for ur response.
Abhinav S 12-Mar-12 3:09am
You are welcome. Vote if it helped.
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

AFAIK triggers are created in database and fires after either insert, update or delete statment. We don't fire it using c#.

Do you want to create triggers through c# code?
AshishMishrag 12-Mar-12 3:03am
thankyou for ur response.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.161021.1 | Last Updated 12 Feb 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100