CREATE TRIGGER with ado.net and C#

Question

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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

Kind regards,
Gift Peddie