CREATE TRIGGER with ado.net and C#

• cew3

  SSC-Addicted

  Points: 493

  More actions

  June 3, 2009 at 2:16 am

  #95654

  Hi,

  I have to crate triggers within my c# program with my SqlConnection and SqlCommand object.

  But when I use the cmd.ExecuteNonQuery with:

  [font="Courier New"]CREATE TRIGGER [dbo].[trg_insert_mytable}]

  ON [dbo].[myTable]

  AFTER INSERT

  AS

  BEGIN

  ...

  END[/font]

  I get the message, that the object 'myTable' is not available. It looks like the statement is not executed within the right context so that the table is not visible.

  The table exists and was just created within my program. Why is the tyble not reachable when I was able to create it in the "same" (?) context of my database???

  Any ideas?

  cew3

• Gift Peddie

  SSC Guru

  Points: 73570

  More actions

  June 3, 2009 at 8:04 am

  #1004033

  You have to use one command object to create the table before you can run the trigger code that will use it because ADO.NET is not connected to SQL Server all the time.

  Kind regards,
  Gift Peddie

• cew3

  SSC-Addicted

  Points: 493

  More actions

  June 4, 2009 at 1:37 am

  #1004454

  I resolved the prolem:

  I had to send a 'USE [myDatabase]' befor the 'Create Trigger...'

  Solution:

  [font="Courier New"]cmd.CommatText = "USE [myDatabase]";

  cmd.ExecuteNonQuery();

  cmd.CommatText = "CREATE TRIGGER ....";

  cmd.ExecuteNonQuery();[/font]

  ---

  Thanks for your reply!

  cew3

• Gift Peddie

  SSC Guru

  Points: 73570

  More actions

  June 5, 2009 at 8:30 am

  #1005318

  So your problem was point of connection instead of delayed execution, glad you have solved your problem.

  Kind regards,
  Gift Peddie