Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2002
    Location
    Brazil
    Posts
    56

    Unanswered: sysprocesses - TRIGGER

    Hy Guys!

    Does anyone knows how to create a trigger in the 'sysprocesses' table?
    I hava already configured SQL Server to allow modifications in systables.

    Is this possible?
    Diogo Hudson Dias
    DBA - SQL 6.5/7.0/2000
    ASBACE - ATP

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    In the past, Microsoft has warned not to add user triggers to system tables. My suggestion would be to login as sa and give it a go. I would be surprised if it work!
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Sep 2002
    Location
    Brazil
    Posts
    56

    Unhappy

    I couldn't create it.

    - Logged as SA, didn't work: ---> Didn't work

    - I've already tried to use SQL Server in Single mode, and logged as SA: ---> Didn't work

    - Tried to create the trigger directly in Sysoobjects: ---> SQL created the procedure, but didn't work.


    I'm trying to filter who is logging in My SQL, killing the SPID ops users that don't complain some requisites of connection.


    Thanks for the help Paul!
    Diogo Hudson Dias
    DBA - SQL 6.5/7.0/2000
    ASBACE - ATP

  4. #4
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163

    Angry

    What Version of SQL server you have?
    What Data Base are You using?

  5. #5
    Join Date
    Sep 2002
    Location
    Brazil
    Posts
    56
    I'm trying to create a trigger in 'sysprocesses' table on the master Database, on SQL Server 2000
    Diogo Hudson Dias
    DBA - SQL 6.5/7.0/2000
    ASBACE - ATP

  6. #6
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163

    Unhappy

    It's not supported for Microsoft SQL Server 2000

    ______________
    Paulo Gonçalves

  7. #7
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    You can alter system tables:

    1. Stop MSSQLSERVER service
    2. Backup MASTER.MDF
    3. Restart MSSQLSERVER service
    4. Enable direct modifications of system catalogs + RECONFIGURE
    5. Update master.dbo.sysobjects column xtype 'S'->'U' for name='sysprocesses'
    6. create trigger on sysprocesses
    7. Update master.dbo.sysobjects column xtype 'U'->'S' for name='sysprocesses'
    8. Disable direct modifications of system catalogs + RECONFIGURE
    9. Look for a prayer-book

  8. #8
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163
    You may be right but after the changes how can you put everithing working like it was, but together with the trigger?
    If "they" say that it's not supported after any change is better you don't nedd to call the "guys", because they wont do any thing.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I had thought that modifying sysprocesses was impossible, because it is not truly a table. It is a "virtual table" that is only created when it is queried.
    Besides, just think of how many updates would go on this table in even a moderately used system. Every change in waittype, every increment in CPU usage, every login. The transaction log for this table alone would be insane.

  10. #10
    Join Date
    Sep 2002
    Location
    Brazil
    Posts
    56

    Don't Work

    I made the test.
    I created the trigger but it didn't fire.
    Diogo Hudson Dias
    DBA - SQL 6.5/7.0/2000
    ASBACE - ATP

  11. #11
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    --It is really a fake table

    use master
    GO
    select case objectproperty(object_id('dbo.sysprocesses'),'Tabl eIsFake') when 1 then 'fake table' when 0 then 'real table' else 'unknown' end

  12. #12
    Join Date
    Feb 2003
    Location
    Irvine, CA
    Posts
    2

    don't need the case text

    Take the word case out of the query to run it successfully.
    ------
    use master
    GO
    select case objectproperty(object_id('dbo.sysprocesses'),'Tabl eIsFake')
    --when 1 then 'fake table' when 0 then 'real table' else 'unknown' end

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •