Solved

Question for vujos

Posted on 2000-04-07
2
242 Views
Last Modified: 2012-05-04
Regarding triggers on system tables, my experience has been as simonsabin says.  I created it but it never fired regardless how i tried.
Please  provide me with the explanation how to create it and have it execute.
0
Comment
Question by:xoxomos
2 Comments
 
LVL 1

Accepted Solution

by:
vujos earned 50 total points
ID: 2697150
Hi,

Following is tested on MSSQL 6.5 (did not have time yet to try on 7.0).

But let's start :

1)<enable update on system tables>

sp_configure 'allow update', 1
reconfigure with override

2)<make syslogins table visible in EM>

update sysobjects set sysstat = 99 where name = 'syslogins' and id = 33

3)<add custom message>

sp_addmessage 50009, 16, 'This is my test message'

4)<create trigger for add, upd of login>

CREATE TRIGGER reminder ON syslogins
FOR INSERT, UPDATE AS RAISERROR (50009, 16, 10)
GO

3)<make syslogins invisible in EM>

update sysobjects set sysstat = 113 where name = 'syslogins' and id = 33

4)<disable system tables updates>

sp_configure 'allow update', 0
reconfigure with override

5)<try to add new login from EM or run>

sp_addlogin 'test1', 'testing', 'master', default


Well there you go, you still think that this was easy ?

General comment is still walid : any playing with system tables should be avoided. If you have to, please backup all the databases (you could end up rebuilding your master db - you know what that is). Please do not try above on production system. This is only an demo.

Hope this helps.

0
 

Author Comment

by:xoxomos
ID: 2700889
O.K.  i'll try that and keep in mind not to do it on a production system ---- just yet.  I'm using 6.5 anyhow.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now