Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Location
    Angola
    Posts
    12

    Smile Unanswered: SQL Queries - Action on count(*)

    Hi There,

    I've got a dts package that goes as follows:

    1. Delete current table column on local server.
    2. Create a connection to production server.
    3. Do the following SQL Statement
    "select *
    from sysdatabases"
    4. Drop it in table on local server where it was deleted in step 1.

    Here comes the problem.
    5. I've got a count(*) of 15 databases but i need to create a trigger upon a higher (>)value of 15 or lower (<) that 15 but not 15.

    6. Once this is complete the rest should be easy to setup ...
    CREATE TRIGGER "Database_Monitoring_MODIFY"
    ON [Master - Table List]
    FOR INSERT, DELETE, UPDATE
    AS EXEC master..xp_sendmail 'xyz',
    'A database modification listing has been detected.'
    GO

    Could anyone please help with a sql statement that i can use in step 5 to recognise if the amount of databases has been changed so that step 6 can activate?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you putting your trigger on sysdatabases?
    I guess you could just write a statement like:

    if (select count(*) from sysdatabases) <> 15
    begin
    --
    --Do your stuff here
    --
    end

    Or you could just set your trigger to fire on Insert and Delete since these would indicate a change in the number of databases.

    blindman

Posting Permissions

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