a rookie question about sp_helptext

From: Jaroslaw Tajcher (ziomuch_at_wp.pl)
Date: 07/21/04 

Date: Wed, 21 Jul 2004 14:25:33 +0200

Hi!
I'm very 'fresh' in DB programming and have one problem...
The problem is that i am to write a function to disable all triggers for
some time.
My idea is to store triggers' creation codes in a seperate table using
sp_helptext, delete triggers and after some time use those codes to restore
triggers. What to do and what data type to use in order to catch the result
table of sp_helptext (all I'm able to do now is to see the table appear on a
screen) and to copy its rows to a temporary table?

Maybe some of you have better ideas to disable triggers...
My system works as follow:
* at the very beginning of transaction a value @@SPID (number of current
thread) is stored in table LOGINS where column USER= id of logged in user;
* values in certain tables are updated - on each table there is set a
trigger ON UPDATE. The trigger checks if the proper @@SPID exists in LOGINS.
If not - an error is being raised.
* a value @@SPID in LOGINS is being cleared.

That works perfectly, but when service want change values directly from
Server Enterprise Manager, the value in LOGINS is not set, and an error
occurs whenever a value is attempted to change. How to disable triggers in
better way, than that I described above?

Thanks!
Yaro

Relevant Pages

  • Re: a rookie question about sp_helptext
    ... See example H in the documentation of "ALTER TABLE" in SQL Server Books ... My idea is to store triggers' creation codes in a seperate table using ... thread) is stored in table LOGINS where column USER= id of logged in user; ... a value @@SPID in LOGINS is being cleared. ...
    (microsoft.public.sqlserver.programming)
  • Re: User and Login auditing
    ... insted of Profiler, since I dont want to run Profiler on a minute to minute ... I would like to be able to report what users and logins I had yesterday and ... It would be easier with Triggers on sysuers and syslogin,s but I have never ... look at Profiler and the Securiity Audit Event ...
    (microsoft.public.sqlserver.security)
  • Re: SPID question
    ... Transactional replication does not fire triggers. ... I am not sure if the context would be the same spid, ...
    (microsoft.public.sqlserver.replication)
  • Re: Session Variables and Triggers
    ... How about grabbing the net_address of the current spid: ... I would like to create triggers that will automatically track ... making SQL system variables useless. ...
    (microsoft.public.sqlserver.security)