Devart Forums

Code: Select all

Query must return exactly one result set - use Execute.

Code: Select all

 CREATE TRIGGER [TABLE]_BI ON [TABLE] INSTEAD OF INSERT as 
BEGIN

      declare @LOCATOR INT
      SET @LOCATOR = (SELECT [KEYFIELD] FROM INSERTED)


      IF @LOCATOR is null or @LOCATOR = 0 BEGIN
      exec UNIQUE_ID [UNIQUEID], @LOCATOR OUTPUT      
      SELECT * INTO #Inserted FROM INSERTED    
      UPDATE #Inserted SET [KEYFIELD] = @LOCATOR  
      SET NOCOUNT ON;    

      INSERT INTO [TABLE] SELECT * FROM #Inserted 

      select [KEYFIELD] as "[KEYFIELD]" from #Inserted               
      END                                                      
      ELSE                                                     
      BEGIN                                                    
      SET NOCOUNT ON;                                          
      INSERT INTO [TABLE] SELECT * FROM INSERTED 
      select [KEYFIELD] as "[KEYFIELD]" from INSERTED    

      END                                                    
END;

Code: Select all

CREATE TABLE [dbo].[TestNumber](
	[TestId] [int] NOT NULL,
	[TestNumber] [int] NOT NULL,
 CONSTRAINT [PK_TestNumber] PRIMARY KEY CLUSTERED 
(
	[TestId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


 CREATE TRIGGER [dbo].[TestNumber_BI] ON [dbo].[TestNumber] INSTEAD OF INSERT as 
BEGIN
	declare @LOCATOR INT
	SET @LOCATOR = (SELECT TestId FROM INSERTED)
	IF @LOCATOR is null or @LOCATOR = 0 
	BEGIN
		declare @GENERATOR_ID INT
		SET @GENERATOR_ID = (SELECT max(TestID) FROM TestNumber) + 1
		
		IF @GENERATOR_ID is null
		BEGIN
			SET @GENERATOR_ID = 1
		END
		   
		SELECT * INTO #Inserted FROM INSERTED    
		UPDATE #Inserted SET TestId = @GENERATOR_ID
		SET NOCOUNT ON;    
		INSERT INTO TestNumber SELECT * FROM #Inserted 
		select TestId as "TestId" from #Inserted               
	END                                                      
	ELSE                                                     
	BEGIN                                                    
		SET NOCOUNT ON;                                          
		INSERT INTO TestNumber SELECT * FROM INSERTED 
		select TestId as "TestId" from INSERTED    
	END                                                    
END;

GO

Code: Select all

procedure TForm1.TestNumber();
var
  fConnection : tUniConnection;
begin
  fConnection := TUniConnection.Create(nil);
  fConnection.Server := [DBSERVER];
  fConnection.Database := [DATABASE];
  fConnection.ProviderName := 'SQL Server';
  fConnection.Username := [DBUSER];
  fConnection.Password := [DBPASS];

  with TUniQuery.Create(nil) do
  begin
    Connection := fConnection;
    sql.Add('insert into TestNumber (TestNumber) values (:TestNumber)');
    params[0].AsInteger := 123;
    prepare;  //works when commented, doesn't work when uncommented
    ExecSql;
    Free();
  end;

  fConnection.free();
end;