Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: 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
good
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.
  Permalink  
Comments
AshishMishrag at 12-Mar-12 3:03am
   
thankyou for ur response.
Rate this: bad
good
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(),
DateModified=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' " +
               "AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'IsMSShipped')=0";
 
//Set value of myconnectionstring to your connection string
string connectionString = "myconnectionstring";
SqlConnection con = new SqlConnection(connectionString);
con.Open();
 
//Declare a DataSet
DataSet dsTrigger = new DataSet();
DataTable dtTables = new DataTable();
 
dsTrigger.Relations.Add("Tables_Keys",
dtTables.Columns["TABLE_NAME"],
dtKeys.Columns["TABLE_NAME"]);
 
//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
parentRow.GetChildRows("Tables_Keys"))
{
      //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");
outSql.Write(content);
outSql.Close();
  Permalink  
Comments
AshishMishrag at 21-Jun-13 2:31am
   
thanks Arunabh it worked for me.
Canny Brisk at 25-Feb-14 4:28am
   
5ed!
 
:)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

  Permalink  
Comments
AshishMishrag at 12-Mar-12 3:03am
   
thankyou for ur response.
Abhinav S at 12-Mar-12 3:09am
   
You are welcome. Vote if it helped.
Rate this: bad
good
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?
  Permalink  
Comments
AshishMishrag at 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
0 OriginalGriff 239
1 Sarvesh Kumar Gupta 185
2 George Jonsson 154
3 ArunRajendra 144
4 Richard MacCutchan 140


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