June 19, 2003 at 5:47 am
Hi!
Trying to create a trigger, that will copy the name of newly added database to a user table from master..sysdatabases:
CREATE TRIGGER add_database ON [dbo].[sysdatabases]
FOR INSERT
AS
insert into MANAGE..BACKUP_DATABASES (dbname)
select [name] from master..sysdatabases
where [name] not in
(select dbname from MANAGE..BACKUP_DATABASES)
Or:
CREATE TRIGGER add_database ON [dbo].[sysdatabases]
FOR INSERT
AS
insert into MANAGE..BACKUP_DATABASES (dbname)
select [name] from inserted
After creating a new database, no data is copied to the user table, but if I run the following from QA:
insert into MANAGE..BACKUP_DATABASES (dbname)
select [name] from master..sysdatabases
where [name] not in
(select dbname from MANAGE..BACKUP_DATABASES)
a new record is added to MANAGE..BACKUP_DATABASES.
June 19, 2003 at 6:18 am
As far as I remember you're not allowed to create triggers on system tables. So are you sure the trigger is created succesfully on sysdatabases?
[font="Verdana"]Markus Bohse[/font]
June 19, 2003 at 6:35 am
It was created, there is a little how to for this:
sp_configure [allow updates], 1
RECONFIGURE WITH OVERRIDE
GO
UPDATE sysobjects
SET xtype='U'
WHERE [name] = 'sysdatabases'
GO
CREATE TRIGGER add_database ON [dbo].[sysdatabases]
FOR INSERT
AS
insert into MANAGE..BACKUP_DATABASES (dbname)
select [name] from master..sysdatabases
where [name] not in
(select dbname from MANAGE..BACKUP_DATABASES)
UPDATE sysobjects
SET xtype='S'
WHERE [name] = 'sysdatabases'
GO
sp_configure [allow updates], 0
RECONFIGURE WITH OVERRIDE
GO
-----------------------------------
I have a few of similar trigger on system tables as sysxlogins and sysjobs,- they work fine...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply