none
using Trigger (inserting Trigger) after insert one record to Table RRS feed

  • Question

  • Hello
    i have two different table. i want to insert them.
    i have below code. inserting same id.  But there is no Trigger. Where should i write trigger ? And how should i use it ?
    Thanks
    using (SqlConnection con = new SqlConnection(conString))
                     {
                         cmd = new SqlCommand("INSERT INTO x_kullanicilar (x_id, x_kod, x_adi, x_sifre) VALUES (@x_id, @x_kod, @x_adi, @x_sifre)", con);
                    SqlParameter id1 = cmd.Parameters.Add("@x_id", SqlDbType.Int);
                    id1.Direction = ParameterDirection.Input;
                    id1.Value = Convert.ToInt32(i);
                    SqlParameter id2 = cmd.Parameters.Add("@x_kod", SqlDbType.VarChar, 50);
                    id2.Direction = ParameterDirection.Input;
                    id2.Value = textBox1.Text;
                    SqlParameter id3 = cmd.Parameters.Add("@x_adi", SqlDbType.VarChar, 50);
                    id3.Direction = ParameterDirection.Input;
                    id3.Value = textBox2.Text;
                    SqlParameter id4 = cmd.Parameters.Add("@x_sifre", SqlDbType.VarChar, 50);
                    id4.Direction = ParameterDirection.Input;
                    id4.Value = textBox3.Text;
    
                     con.Open();cmd.ExecuteNonQuery();con.Close();
                    con.Dispose();
                    
                    idL.Text = i.ToString();
                     }
    
    using (SqlConnection con = new SqlConnection(conString))
                        {
                            urun u = (urun)listBox1.SelectedItems[i];
    
                            cmd = new SqlCommand("INSERT INTO x_firmasecimi (x_firmaid, x_firmaadi, x_kullaniciid) VALUES (@x_firmaid, @x_firmaadi, @x_kullaniciid)", con);
                            SqlParameter id1 = cmd.Parameters.Add("@x_firmaid", SqlDbType.Int);
                            id1.Direction = ParameterDirection.Input;
                            id1.Value = Convert.ToInt32(i);
                            SqlParameter id2 = cmd.Parameters.Add("@x_firmaadi", SqlDbType.VarChar, 50);
                            id2.Direction = ParameterDirection.Input;
                            id2.Value = u.Ad;
                            SqlParameter id3 = cmd.Parameters.Add("@x_kullaniciid", SqlDbType.Int);
                            id3.Direction = ParameterDirection.Input;
                            id3.Value = genel.kullanici.kullaniciid;
                           
                             con.Open();cmd.ExecuteNonQuery();con.Close();
                            con.Dispose();
    
    
    
    Wednesday, December 30, 2009 4:14 PM

Answers

  • A trigger would be written in SQL Server against a specific table.  But I'm unclear on what you want to use a trigger for.  Can you provide more details?


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    • Marked as answer by JuniorCsharp Saturday, January 2, 2010 7:48 AM
    Wednesday, December 30, 2009 4:24 PM
    Moderator
  • Please read the books on-line article for creating a trigger.  There are examples at the end of the page.  http://technet.microsoft.com/en-us/library/ms189799.aspx


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    • Marked as answer by JuniorCsharp Saturday, January 2, 2010 7:31 AM
    Wednesday, December 30, 2009 5:19 PM
    Moderator
  • If you are using SQL Server 2005/2008, you can use the OUTPUT clause of the INSERT statement to insert data into  the second table.

    Refer to SQL Server Books Online, Topic: "OUTPUT clause"

    Here is an example of using the OUTPUT clause to INSERT data into two tables with one INSERT statement.

    USE tempdb;

    GO

     

    SET NOCOUNT ON;

     

    DECLARE @MyTable table

       (  RowId   int  IDENTITY(101,1),

          MyData varchar(20)

       );

     

    DECLARE @MyArchiveTable table

       (  ArchiveID    int  IDENTITY,

          RowId        int,

          MyData       varchar(20),

          ArchiveDate  datetime,

          ArchiveUser  varchar(50)

       );

     

    INSERT INTO @MyTable

          OUTPUT

             inserted.RowId,

             inserted.MyData,

             getdate(),

             system_user

          INTO @MyArchiveTable

       VALUES

          ( 'This is my Test Data' );

     

    SELECT

       RowID,

       MyData

    FROM @MyTable;

     

    SELECT

       ArchiveID,

       RowID,

       MyData,

       ArchiveDate,

       ArchiveUser

    FROM @MyArchiveTable;

     

    /*

    RowID  t1Value

    101    This is my Test Data

    */

     

    /*

    ArchiveID RowID MyData               ArchiveDate             ArchiveUser

    1         101   This is my Test Data 2009-03-11 15:24:35.723 WESTWOOD\Arnie

    */

     

     

     


    "You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson
    • Marked as answer by JuniorCsharp Saturday, January 2, 2010 7:31 AM
    Friday, January 1, 2010 6:13 PM
    Moderator
  • Hi,

    If I understand you correctly, You want to Insert a record into a table say Table 1 (A , B ,C) and also into Table 2(A, D, E, F) with a single insert command from your ADO.NET code and only one column is common between those two tables (ex: 'A').

    First of all Triggers are automatically called by SQL Server whenever there is an event like INSERT, UPDATE, DELETE on a table is done. User can not call them explicitly by using the client code and you can not pass parameters to triggers.

    Having said that; for your scenario Triggers doesnt make sense because you can not pass parameters i.e. to insert data in to D,E,F columns in Table 2. So the best solution is create a procedure which takes parameters (A, B,C,D,E,F) and put your insert statements inside it and call the procedure from your client code (ADO.NET). So one call from client code.


    Hope it helps.
    • Marked as answer by JuniorCsharp Saturday, January 2, 2010 7:30 AM
    Saturday, January 2, 2010 7:05 AM

All replies

  • A trigger would be written in SQL Server against a specific table.  But I'm unclear on what you want to use a trigger for.  Can you provide more details?


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    • Marked as answer by JuniorCsharp Saturday, January 2, 2010 7:48 AM
    Wednesday, December 30, 2009 4:24 PM
    Moderator
  • Hello
    i read an article about Trigger. it says that Trigger provide "referential integrity". When 2 or more inserting to 2 or 3 tables, we should use Trigger.
    So i will insert one record to Two tables. Tables columns are different.
    Should i use two inserting codes with Ado.Net ?
    Or Should i use one inserting (Ado. Net) with one Trigger ?
    Thanks
    Wednesday, December 30, 2009 4:37 PM
  • What is the relationship between the two tables in your example above?  I don't see a common column.


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Wednesday, December 30, 2009 4:42 PM
    Moderator
  • x_id and x_firmaid will have same value.
    one of them at first table and second of them at second table.
    Another columns arent be same.
    Wednesday, December 30, 2009 4:54 PM
  • If the tables are related, may be you want to insert information into both of them in one SP (parent first, then child).

    See a sample here How to insert information into multiple related tables and return ID using SQLDataSource
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, December 30, 2009 4:57 PM
    Moderator
  • hi Naom. I have read ur link.
    But i only use Stored Procedure for the Select Query. (For fast)
    i use dynamic Ado.Net for Delete, Update and Insert. (Because fast is enough for me.)

    But i want to use Trigger after inserting one table. then i want to use Update Trigger.
    My sceniro is
    first i will insert a record at first table.
    second i will insert a record at second table with Trigger.
    third i will update a record at third table with Trigger.

    They should be okey. And there should not data loss. if i use only ado.net, maybe sometimes one of inserting or updating dont be okey.
    Wednesday, December 30, 2009 5:10 PM
  • Please read the books on-line article for creating a trigger.  There are examples at the end of the page.  http://technet.microsoft.com/en-us/library/ms189799.aspx


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    • Marked as answer by JuniorCsharp Saturday, January 2, 2010 7:31 AM
    Wednesday, December 30, 2009 5:19 PM
    Moderator
  • Hello
    i looked at examples. But i couldnt find what i look for.
    My codes should be like.
    Button_click
    1-) inserting with Ado.net for first table. it is Bill Table. Only one record.
    2-)run Trigger at here for inserting for second table. There should be paramaters. or how should i send value for Trigger. it is goods movement Table. one or more records inserting related to good movement. (Stock Transaction Table)
    3-)run Trigger at here for updating for third table. only one record. it is Stock Table.  There should be paramaters. How can i send value for third Trigger ?
    4-)run Trigger at here for updatind for fourth table. only one record. it is Current Table.  There should be paramaters. How can i send value for fourt  Trigger ?

    How can i do this ?
    Also Should i use Trigger for this ?
    Or Should i use one Stored Procedure for this ? if i use one Stored Procedure for this, how can i insert multirecord for my 2-) condition ?

    Thanks

    Thursday, December 31, 2009 8:24 AM
  • If you are using SQL Server 2005/2008, you can use the OUTPUT clause of the INSERT statement to insert data into  the second table.

    Refer to SQL Server Books Online, Topic: "OUTPUT clause"

    Here is an example of using the OUTPUT clause to INSERT data into two tables with one INSERT statement.

    USE tempdb;

    GO

     

    SET NOCOUNT ON;

     

    DECLARE @MyTable table

       (  RowId   int  IDENTITY(101,1),

          MyData varchar(20)

       );

     

    DECLARE @MyArchiveTable table

       (  ArchiveID    int  IDENTITY,

          RowId        int,

          MyData       varchar(20),

          ArchiveDate  datetime,

          ArchiveUser  varchar(50)

       );

     

    INSERT INTO @MyTable

          OUTPUT

             inserted.RowId,

             inserted.MyData,

             getdate(),

             system_user

          INTO @MyArchiveTable

       VALUES

          ( 'This is my Test Data' );

     

    SELECT

       RowID,

       MyData

    FROM @MyTable;

     

    SELECT

       ArchiveID,

       RowID,

       MyData,

       ArchiveDate,

       ArchiveUser

    FROM @MyArchiveTable;

     

    /*

    RowID  t1Value

    101    This is my Test Data

    */

     

    /*

    ArchiveID RowID MyData               ArchiveDate             ArchiveUser

    1         101   This is my Test Data 2009-03-11 15:24:35.723 WESTWOOD\Arnie

    */

     

     

     


    "You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson
    • Marked as answer by JuniorCsharp Saturday, January 2, 2010 7:31 AM
    Friday, January 1, 2010 6:13 PM
    Moderator
  • Hi,

    If I understand you correctly, You want to Insert a record into a table say Table 1 (A , B ,C) and also into Table 2(A, D, E, F) with a single insert command from your ADO.NET code and only one column is common between those two tables (ex: 'A').

    First of all Triggers are automatically called by SQL Server whenever there is an event like INSERT, UPDATE, DELETE on a table is done. User can not call them explicitly by using the client code and you can not pass parameters to triggers.

    Having said that; for your scenario Triggers doesnt make sense because you can not pass parameters i.e. to insert data in to D,E,F columns in Table 2. So the best solution is create a procedure which takes parameters (A, B,C,D,E,F) and put your insert statements inside it and call the procedure from your client code (ADO.NET). So one call from client code.


    Hope it helps.
    • Marked as answer by JuniorCsharp Saturday, January 2, 2010 7:30 AM
    Saturday, January 2, 2010 7:05 AM
  • Please see my last reply in this thread http://forums.asp.net/t/1508142.aspx with the links to all solutions you can use for your problem.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, January 4, 2010 3:20 PM
    Moderator