Creating trigger at runtime

Hi,
I am using VS 2005 and SQL server 2005.
I want to create a trigger at runtime. The trigger has to be created
when one of the rows in user-specified table is deleted. User will
specify the name of the table at runtime. Hence I cant create trigger
at development time. Can I create such a trigger from my C# code?
ManaMana wrote:
> Hi,
> I am using VS 2005 and SQL server 2005.
> I want to create a trigger at runtime. The trigger has to be created
> when one of the rows in user-specified table is deleted. User will
> specify the name of the table at runtime. Hence I cant create trigger
> at development time. Can I create such a trigger from my C# code?
> Mana
Create triggers when your tables are created. Do you really create
tables at runtime? This sounds like a strange requirement and perhaps
not an optimal design.
Use the CREATE TRIGGER statement to create a trigger. Bear in mind that
you'll have to give ddl admin rights to your users, which is not
usually recommended. Maybe if you explain a bit more about why you want
to do this then someone can suggest a better alternative.
David Portas
SQL Server MVP
--|||Mana
Can I ask you , why an user should specify a table name when he/she deletes
a row?
"Mana" <DearManasi@.gmail.com> wrote in message
news:1136290048.321123.94070@.o13g2000cwo.googlegroups.com...
> Hi,
> I am using VS 2005 and SQL server 2005.
> I want to create a trigger at runtime. The trigger has to be created
> when one of the rows in user-specified table is deleted. User will
> specify the name of the table at runtime. Hence I cant create trigger
> at development time. Can I create such a trigger from my C# code?
> Mana
>|||Triggers can be created in C# code if you have the proper permissions... But
I would warn against doing this... IF there is something you wish to do
permanently, just add the trigger and leave it there... Otherwise put the
code directly in the C# program, instead of creating a trigger...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Mana" wrote:

> Hi,
> I am using VS 2005 and SQL server 2005.
> I want to create a trigger at runtime. The trigger has to be created
> when one of the rows in user-specified table is deleted. User will
> specify the name of the table at runtime. Hence I cant create trigger
> at development time. Can I create such a trigger from my C# code?
> Mana
>|||Hi Uri n David,
Let me explain my requirement elaborately.
I am building a web application which allows subscribers to subscribe
for the event of thier interest and receive notification when the event
occurs.
The event can be inserting or deleting a record from one of the tables
in database. (This is equirement of client. Cant help)The names of the
tables are exposed to user. And the user decides at runtime - first,
the name of the table and second, whether he should be notified on
insertion of record or deletion.
Since I dont not know table name and action (insert / delete) in
advance I cant write trigger in advance. I need to do it runtime.
I dont want to create triggers for all the tables existing in database.
Only for those that user is interested in.
Hope this explains my problem.
Mana|||Mana
I'd create one general table for auditing. When inserting/deletion is
occured so insert a row into this table which may have for instance a name
of the table , type of the operation and etc
"Mana" <DearManasi@.gmail.com> wrote in message
news:1136354013.575930.55610@.g14g2000cwa.googlegroups.com...
> Hi Uri n David,
> Let me explain my requirement elaborately.
> I am building a web application which allows subscribers to subscribe
> for the event of thier interest and receive notification when the event
> occurs.
> The event can be inserting or deleting a record from one of the tables
> in database. (This is equirement of client. Cant help)The names of the
> tables are exposed to user. And the user decides at runtime - first,
> the name of the table and second, whether he should be notified on
> insertion of record or deletion.
> Since I dont not know table name and action (insert / delete) in
> advance I cant write trigger in advance. I need to do it runtime.
> I dont want to create triggers for all the tables existing in database.
> Only for those that user is interested in.
> Hope this explains my problem.
> Mana
>|||Mana wrote:
> Hi Uri n David,
> Let me explain my requirement elaborately.
> I am building a web application which allows subscribers to subscribe
> for the event of thier interest and receive notification when the event
> occurs.
> The event can be inserting or deleting a record from one of the tables
> in database. (This is equirement of client. Cant help)The names of the
> tables are exposed to user. And the user decides at runtime - first,
> the name of the table and second, whether he should be notified on
> insertion of record or deletion.
> Since I dont not know table name and action (insert / delete) in
> advance I cant write trigger in advance. I need to do it runtime.
> I dont want to create triggers for all the tables existing in database.
> Only for those that user is interested in.
> Hope this explains my problem.
> Mana
Don't send notifications from a trigger. I explained why not in the
following thread:
http://groups.google.co.uk/group/mi...4dd2078d0df5312
Uri's suggestion is a better one: Log your changes to a table. Send out
notifications at suitable intervals based on that table. Have you
considered using SQL Server Notification Services? Both 2000 and 2005
versions are available. See:
http://www.microsoft.com/sql/techno...on/default.mspx
Hope this helps.
David Portas
SQL Server MVP
--|||Thanks, I think Uri's sugestion to keep an audit table is good.
Will implement that.
Thanks to all :)
Mana