SEO

#1 (**permalink**) 02-28-2008, 05:00 PM

..NET Developer (pleasereplytogroups@hotmail.com) writes:
> I would like to create my first simple trigger, but when I try to save
> the trigger inside Enterprise Manager, it gives me the following
> error:
>
> Error 21001 : [SQL-DMO] Stored procedure definition must include name
> and text (for Standard StoredProcedure) or libraryname (for Extended
> StoredProcedure).
>
> The actual trigger itself is, I think, fairly straight forward:
>
> CREATE trigger UpdateCache
> on MyTable
> for update, delete, insert
> as
> declare @cmd varchar(200)
> select @cmd = 'echo ' + cast(getDate() as varchar(50)) + ' >
> c:\tablechange.txt'
> exec master..xp_cmdshell @cmd, no_output

I don't know what you plan to do for real in your trigger, but be wary
with calling xp_cmdshell from a trigger. A trigger executes in the
context of a transaction, and you are holding locks. If the trigger
runs for too long, and it is an intensive system, you may block other
processes.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

#2 (**permalink**) 02-28-2008, 05:01 PM

Thanks a lot for your advice Erland - the trigger code in question was
actually copied from an example in a book that I'm reading: ASP.NET
Unleashed. It's just a "quick example" I guess.
Best regards from Robert.

Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns93ABF314CC5ADYazorman@127.0.0.1>...
> .NET Developer (pleasereplytogroups@hotmail.com) writes:
> > I would like to create my first simple trigger, but when I try to save
> > the trigger inside Enterprise Manager, it gives me the following
> > error:
> >
> > Error 21001 : [SQL-DMO] Stored procedure definition must include name
> > and text (for Standard StoredProcedure) or libraryname (for Extended
> > StoredProcedure).
> >
> > The actual trigger itself is, I think, fairly straight forward:
> >
> > CREATE trigger UpdateCache
> > on MyTable
> > for update, delete, insert
> > as
> > declare @cmd varchar(200)
> > select @cmd = 'echo ' + cast(getDate() as varchar(50)) + ' >
> > c:\tablechange.txt'
> > exec master..xp_cmdshell @cmd, no_output
>
> I don't know what you plan to do for real in your trigger, but be wary
> with calling xp_cmdshell from a trigger. A trigger executes in the
> context of a transaction, and you are holding locks. If the trigger
> runs for too long, and it is an intensive system, you may block other
> processes.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Re: Newbie triggers help required please.