Create Trigger from Delphi Code using Native Client Driver

Question

I have an insert trigger that contains dynamic sql stored as a resource for my XE2 project. It also contains placeholders for the database name and table name that are substituted when the Delph code runs to execute the SQL.

Originally I was using the DevArt SQL Server driver against a SqlExpress database, but am now wanting to use ODBC and the SQL Native Client driver against a LocalDB database.

What I have found is that my original create trigger scripts no longer work.

I am using TSQLQuery.ExecSQL to execute my SQL commands.

CREATE TRIGGER [dbo].[#TableName#_INSERT_TRIGGER] ON [#DatabaseName#].[dbo].[#TableName#] FOR INSERT

causes a

'[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot create trigger on 'EvaluationCompany_COPYDB.dbo.COPY_PRODUCTS' as the target is not in the current database.'

The parser class I use does split SQL scripts at the GO keyword into separate statements, so I amended my create trigger script to say

USE [#DatabaseName#]
GO
CREATE TRIGGER [dbo].[#TableName#_INSERT_TRIGGER] ON [dbo].[#TableName#] FOR INSERT

which is what you would do in SSMS but that says

'[Microsoft][SQL Server Native Client 11.0][SQL Server]The object 'dbo.COPY_PRODUCTS' does not exist or is invalid for this operation.'

Probably because the "current database" for the CREATE is not the one set by USE, as it seems to be forgotten.

I tried my usual way of stringing sql statements into a single execute by doing

USE [#DatabaseName#];
CREATE TRIGGER [dbo].[#TableName#_INSERT_TRIGGER] ON [dbo].[#TableName#] FOR INSERT

but that throws the expected

'[Microsoft][SQL Server Native Client 11.0][SQL Server]'CREATE TRIGGER' must be the first statement in a query batch.'

So I wrapped the whole CREATE...END in a EXEC [#DatabaseName#].[sys].[sp_ExecuteSQL] N' ' and tried to execute that. If I paste the contents of the string variable into SSMS it executes fine, but when passed to the ExecSQL, it says

'[Microsoft][SQL Server Native Client 11.0][SQL Server]The request for procedure 'sp_executesql' failed because 'sp_executesql' is a procedure object.'

which is kind of nonsensical. So now I am at a loss as to how to create a trigger on a table using dbExpress and the SQL Server native client.

Answer 1

I'm not sure exactly what the problem is, but I was able to successfully run the following code. Maybe it will give you a clue.

uses
  Data.Win.AdoDB;

procedure TForm1.Button1Click(Sender: TObject);
var
  aConnection : tAdoConnection;

  procedure InitializeAdoConnection;
  begin
    aConnection := tAdoConnection . Create ( self );

    with aConnection do
      begin
        ConnectionString :=   'Provider=MSDASQL.1;'
                            + 'Password=' + gPassword + ';'
                            + 'Persist Security Info=True;'
                            + 'User ID=' + gUserName + ';'
                            + 'Data Source=' + gOdbcAlias + ';'
                            + 'Extended Properties="DSN=' + gOdbcAlias + ';'
                            + 'UID=' + gUserName + ';'
                            + 'PWD=' + gPassword + ';'
                            + 'APP=Enterprise;'
                            + 'WSID=' + gMachineName + ';'
                            + 'DATABASE=master";'
                            + 'Initial Catalog=master';
        LoginPrompt := false;
        Connected := true;
      end;
  end;

  procedure ExecuteCommand ( const nSqlCommand : string );
  begin
    with tAdoCommand . Create ( nil ) do
      try
        Connection := aConnection;
        CommandText := nSqlCommand;
        Execute;
      finally
        Free;
      end;
  end;

  procedure QueryResults;
  begin
    with tAdoQuery . Create ( nil ) do
      try
        Connection := aConnection;
        SQL . Text := 'select * from COPY_PRODUCTS';
        Open;

        while not EOF do
          begin
            Memo1 . Lines . Add ( 'ID='
                  + inttostr ( FieldByName ( 'PRODUCT_ID' ) . AsInteger )
                  + ' Name='
                  + FieldByName ( 'PRODUCT_NAME' ) . AsString );

            Next;
          end;


      finally
        Free;
      end;
  end;

begin
  InitializeAdoConnection;

//  ExecuteCommand ( 'drop database EvaluationCompany_COPYDB' );

  ExecuteCommand ( 'create database EvaluationCompany_COPYDB' );

  ExecuteCommand ( 'use EvaluationCompany_COPYDB' );

  ExecuteCommand ( 'create table dbo.COPY_PRODUCTS '
                   + '( PRODUCT_ID int identity(1,1),'
                   + '  PRODUCT_NAME varchar(50) )' );

  ExecuteCommand ( 'create trigger dbo.COPY_PRODUCTS_INSERT_TRIGGER '
                   + 'on dbo.COPY_PRODUCTS '
                   + 'for insert '
                   + 'as '
                   + 'begin '
                   + '  update COPY_PRODUCTS '
                   + '    set PRODUCT_NAME = PRODUCT_NAME + ''!'' '
                   + '    where PRODUCT_ID in '
                   + '    ( select PRODUCT_ID from INSERTED )'
                   + 'end ' );

  ExecuteCommand ( 'insert into COPY_PRODUCTS ( product_name ) '
                   + 'values ( ''Stacky Goodness'' ) ' );

  QueryResults;
end;