Very Computer

> Hi,

> i need to update all rows in table. To accelerate updates i save all table
> triggers on in temporary table and try to restore it after updating.
> See sample of code:

> /*
>  CREATE TABLE #T
>    (sid UNIQUEIDENTIFIER,
>    tbname VARCHAR (255),
>    trname VARCHAR (255),
>    trtext TEXT)
> */

temp
> table #T

> UPDATE ......

> -- now i try to retrieve trigger text from temp table #T and execute it as
> SQL statement

> It works only if length of trtext less than 8000.

> What can i do if my trigger have size more then 8000 characters?

> Konstantin.

> Hi,

> i need to update all rows in table. To accelerate updates i save all table
> triggers on in temporary table and try to restore it after updating.
> See sample of code:

> /*
>  CREATE TABLE #T
>    (sid UNIQUEIDENTIFIER,
>    tbname VARCHAR (255),
>    trname VARCHAR (255),
>    trtext TEXT)
> */

temp
> table #T

> UPDATE ......

> -- now i try to retrieve trigger text from temp table #T and execute it as
> SQL statement

> It works only if length of trtext less than 8000.

> What can i do if my trigger have size more then 8000 characters?

> Konstantin.

> If I understand what you are trying to do, there is no need to drop and
> re-create the triggers.  It is not a valid approach since another process
> may run at the same time as the one that dropped the trigger and then what
?

> Check SQL Server books online under ALTER TABLE, specifically the { ENABLE
|
> DISABLE } TRIGGER
>         { ALL | trigger_name [ ,...n ] } section

> --
> HTH,
> David Satz
> Principal Web Engineer
> Hyperion Solutions
> { SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
> (Please reply to group only - emails answered rarely)
> -----------------------------------------------------------------


> > Hi,

> > i need to update all rows in table. To accelerate updates i save all
table
> > triggers on in temporary table and try to restore it after updating.
> > See sample of code:

> > /*
> >  CREATE TABLE #T
> >    (sid UNIQUEIDENTIFIER,
> >    tbname VARCHAR (255),
> >    trname VARCHAR (255),
> >    trtext TEXT)
> > */

> temp
> > table #T

> > UPDATE ......

> > -- now i try to retrieve trigger text from temp table #T and execute it
as
> > SQL statement

> > It works only if length of trtext less than 8000.

> > What can i do if my trigger have size more then 8000 characters?

> > Konstantin.

> Konstantin,

> You can save the triggers by copying rows from the syscomments system
table
> in your temp table. There a trigger can span more than one row if the text
> is too long. The text in exach row can be read in a memory variable. In
the
> EXECUTE statement you can concatenate two or more string variables,

> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org

> > Hi,

> > i need to update all rows in table. To accelerate updates i save all
table
> > triggers on in temporary table and try to restore it after updating.
> > See sample of code:

> > /*
> >  CREATE TABLE #T
> >    (sid UNIQUEIDENTIFIER,
> >    tbname VARCHAR (255),
> >    trname VARCHAR (255),
> >    trtext TEXT)
> > */

> temp
> > table #T

> > UPDATE ......

> > -- now i try to retrieve trigger text from temp table #T and execute it
as
> > SQL statement

> > It works only if length of trtext less than 8000.

> > What can i do if my trigger have size more then 8000 characters?

> > Konstantin.

>-----Original Message-----
>Hi,

>i need to update all rows in table. To accelerate updates
i save all table
>triggers on in temporary table and try to restore it
after updating.
>See sample of code:

>/*
> CREATE TABLE #T
>   (sid UNIQUEIDENTIFIER,
>   tbname VARCHAR (255),
>   trname VARCHAR (255),
>   trtext TEXT)
>*/

triggets into temp
>table #T


>UPDATE ......

>-- now i try to retrieve trigger text from temp table #T
and execute it as
>SQL statement

>It works only if length of trtext less than 8000.

>What can i do if my trigger have size more then 8000
characters?

>Konstantin.

>.

> Thanks Dejan!

> i know i may put trigger lines into table separately (now i put its as
> concatenation of all rows),
> but how can i know how much variables i need in EXEC statement

> Konstantin.

> ???Y/???Y ????? ???Y??:

> > Konstantin,

> > You can save the triggers by copying rows from the syscomments system
> table
> > in your temp table. There a trigger can span more than one row if the
text
> > is too long. The text in exach row can be read in a memory variable. In
> the
> > EXECUTE statement you can concatenate two or more string variables,

> > --
> > Dejan Sarka, SQL Server MVP
> > FAQ from Neil & others at: http://www.sqlserverfaq.com
> > Please reply only to the newsgroups.
> > PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org

> > > Hi,

> > > i need to update all rows in table. To accelerate updates i save all
> table
> > > triggers on in temporary table and try to restore it after updating.
> > > See sample of code:

> > > /*
> > >  CREATE TABLE #T
> > >    (sid UNIQUEIDENTIFIER,
> > >    tbname VARCHAR (255),
> > >    trname VARCHAR (255),
> > >    trtext TEXT)
> > > */

into
> > temp
> > > table #T


> > > UPDATE ......

> > > -- now i try to retrieve trigger text from temp table #T and execute
it
> as
> > > SQL statement

> > > It works only if length of trtext less than 8000.

> > > What can i do if my trigger have size more then 8000 characters?

> > > Konstantin.

> What about this.

> alter table <TABLE_NAME> disable trigger all
> update ...
> alter table <TABLE_NAME> enable trigger all

> Martin

> >-----Original Message-----
> >Hi,

> >i need to update all rows in table. To accelerate updates
> i save all table
> >triggers on in temporary table and try to restore it
> after updating.
> >See sample of code:

> >/*
> > CREATE TABLE #T
> >   (sid UNIQUEIDENTIFIER,
> >   tbname VARCHAR (255),
> >   trname VARCHAR (255),
> >   trtext TEXT)
> >*/

> triggets into temp
> >table #T


> >UPDATE ......

> >-- now i try to retrieve trigger text from temp table #T
> and execute it as
> >SQL statement

> >It works only if length of trtext less than 8000.

> >What can i do if my trigger have size more then 8000
> characters?

> >Konstantin.

> >.

> If I understand what you are trying to do, there is no need to drop and
> re-create the triggers.  It is not a valid approach since another process
> may run at the same time as the one that dropped the trigger and then what
>?

> Check SQL Server books online under ALTER TABLE, specifically the
> { ENABLE |  DISABLE } TRIGGER
>         { ALL | trigger_name [ ,...n ] } section