Simple Insertion of records

[JayKusch]

TableA has database name column and active\inactive flag

TableB has database name

When we inactivate database(s) in TableA we want code later on to insert the newly deactivated database(s) name(s) into TableB.

I know code with use an IF NOT EXIST on the TableB insertion code, just not able to get it to fly right.

Need more coffee - lol


Thanks

J. Kusch

 

[NoCoolHandle]

try this out...

create table dbs
(databasename varchar(300),
 active bit)
 create table olddbs
 (databasename varchar(300))
 go
 create trigger dbs_inactive
 on dbs
 for update
 as
 begin
	insert into olddbs (databasename) select databasename name from inserted where active=0
 end
 
 go
 insert into dbs(databasename,active) values  ('test1',1)
 insert into dbs(databasename,active) values  ('test2',1)
 insert into dbs(databasename,active) values  ('test3',1)
 insert into dbs(databasename,active) values  ('test4',1)
 go
 
 update dbs set active=0 where databasename='test3'
 go
 select * from olddbs

the result is a simple record being added for olddbs. The nice thing is that it will easily add multiple records.. No need for an exists test, just a simple select.

 

[JayKusch]

Ahhh ... one issue ... using SysDatabases (system table) so we cannot add that trigger.

Looking for code that would look something like:

If database(s) in sysdatabases set to offline then add database NAME from SysDatabases to TableB IF it does not already exist.

If it exists already in TableB ... disregard

The code will be run each night to pick up databases that have been turned offline and then added to TableB.

Hope that clears it up a litte more.

Thanks for the help thus far



Thanks

J. Kusch

 

[NoCoolHandle]

Look up DDL trigger.

"Data definition Trigger"
it is designed to trap create/alter - db/table/function etc. type events..

This should solve your issues.

 

[JayKusch]

Looking to stay away from triggers.

Want to do this code-wise.

I will keep working at it.

Thanks

Thanks

J. Kusch

 

[JayKusch]

OK ... figured it out ...

INSERT INTO TableB
(DBName, Field1, Field2, Field3)
  SELECT [Name],
  	 '',
	 0,
	 0	
  FROM	Master.dbo.SysDatabases
  WHERE NOT EXISTS (SELECT *
		    FROM   TableB
		    WHERE TableB.DBName =   Master.dbo.SysDatabases.[Name])	
	AND	Status = 536

Thanks

J. Kusch