Populate schema_only table with some initial values

Question

Is it possible populate a schema_only table (with some initial values) when the database starts, e.g. by invoking a stored procedure?

The logic that normally operates on this table would be more complicated if I had to detect whether a cold start had occurred.

edit:

It seems like sp_procoption gets me halfway there. However, the stored procedure configured this way is not executed when ALTER DATABASE <dbname> SET ONLINE; is run. Which is a bummer because the data does go away when ALTER DATABASE <dbname> SET OFFLINE; is run.

Answer 1

You can detect ALTER DATABASE <dbname> SET ONLINE; statements with DDL trigger for ALTER_DATABASE event type. The difficult part is to find when it's state is changed from OFFLINE to ONLINE (instead of some other ALTER DATABASE statement, like MODIFY FILE for example). When the trigger is fired, EVENTDATA() function will return XML like these:

<EVENT_INSTANCE>
  <EventType>ALTER_DATABASE</EventType>
  <PostTime>2018-12-17T16:26:25.250</PostTime>
  <SPID>80</SPID>
  <ServerName>xxxxxxx</ServerName>
  <LoginName>xxxxxxxxxxxxxxxxxx</LoginName>
  <DatabaseName>xxxxx</DatabaseName>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>ALTER DATABASE xxxxx SET OFFLINE</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>


<EVENT_INSTANCE>
  <EventType>ALTER_DATABASE</EventType>
  <PostTime>2018-12-17T16:26:36.953</PostTime>
  <SPID>80</SPID>
  <ServerName>xxxxxxx</ServerName>
  <LoginName>xxxxxxxxxxxxxxxxxx</LoginName>
  <DatabaseName>xxxxx</DatabaseName>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>ALTER DATABASE xxxxx SET ONLINE</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

In theory you can try to parse <CommandText>, but it may not be as easy as it sounds. Instead of that, you can check is your database currently ONLINE and are there any rows in the schema_only table. Of course, in the trigger you should also check is this event related to your database. So the trigger could look something like this:

CREATE TRIGGER DDL_ALTER_DATABASE_TRIGGER
ON ALL Server
FOR ALTER_DATABASE
AS
BEGIN
    declare @DatabaseName nvarchar(200), @TSQL nvarchar(2000), @event XML

    select @event = EVENTDATA()

    select @DatabaseName = @event.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(200)' )
    select @TSQL = @event.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(2000)' ) -- Check the command text if you want

    if @DatabaseName = '<my database name>'
    begin
        declare @DatabaseCurrentState int

        select @DatabaseCurrentState = state
        from sys.databases
        where name = '<my database name>'

        if @DatabaseCurrentState = 0 -- It is ONLINE now
        begin
            if not exists(select * from [<my database name>].schema.schema_only_table)
            begin
                insert into [<my database name>].schema.schema_only_table(field1, field2)
                values(1, 2)
                -- or simply execute your auto executed stored procedure here
            end
        end
    end
END