What happened?
Hey,
I have three tables User, Building and UserBuilding. I want to create a trigger that creates a userBuilding object once a building is created. However, once I run the following mutation:
export const CREATE_BUILDING = gql`
mutation CreateBuilding(
$street: String!
$city: String!
$latitude: Float!
$longitude: Float!
$metadata: String!
$heatingType: String!
) {
createBuilding(
item: {
street: $street
city: $city
latitude: $latitude
longitude: $longitude
metadata: $metadata
heating_type: $heatingType
}
) {
id
street
city
latitude
longitude
metadata
}
}
`;
which executes fine without the trigger, I get the error shown at the bottom. The gist ist The target table 'dbo.Building' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.. I see the error should be addressed by #1630 but I'm still getting the error in v0.8.51.
These are the sql schemas in case they are relevant:
CREATE TABLE [dbo].[Building] (
[id] INT IDENTITY (1, 1) NOT NULL,
[street] NVARCHAR (50) NULL,
[city] NVARCHAR (50) NULL,
CONSTRAINT [PK_Building] PRIMARY KEY CLUSTERED ([id] ASC)
);
CREATE TABLE [dbo].[User]
(
[id] NVARCHAR (36) NOT NULL,
[email] NVARCHAR (100) NOT NULL,
[first_name] NVARCHAR (50) NULL,
[last_name] NVARCHAR (50) NULL,
[role] NVARCHAR (50) NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ([id] ASC)
)
CREATE TABLE [dbo].[UserBuilding] (
[user_id] NVARCHAR(36) NOT NULL,
[building_id] INT NOT NULL,
[permission_id] INT DEFAULT 1 NOT NULL,
PRIMARY KEY (user_id, building_id, permission_id),
CONSTRAINT [FK_UserBuilding_User] FOREIGN KEY (user_id) REFERENCES [User](id),
CONSTRAINT [FK_UserBuilding_Building] FOREIGN KEY (building_id) REFERENCES [Building](id),
CONSTRAINT [FK_UserBuilding_Permission] FOREIGN KEY (permission_id) REFERENCES [Permission](id)
);and the trigger
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tr_AfterInsertBuilding]
ON [dbo].[Building]
AFTER INSERT
AS
BEGIN
-- Declare a variable to hold the user_id from the session context
DECLARE @userId NVARCHAR(36);
-- Get the user_id from the session context
SET @userId = CONVERT(NVARCHAR(36), SESSION_CONTEXT(N'userId'));
-- Insert new record in UserBuilding table for the newly inserted Building
INSERT INTO [dbo].[UserBuilding] (user_id, building_id, permission_id)
SELECT @userId, i.id, 1
FROM INSERTED i;
END;
GO
ALTER TABLE [dbo].[Building] ENABLE TRIGGER [tr_AfterInsertBuilding]
GO
Version
0.8.51
What database are you using?
Azure SQL
What hosting model are you using?
Static Web Apps (SWA)
Which API approach are you accessing DAB through?
GraphQL
Relevant log output
[dataApi] info: Microsoft.AspNetCore.Hosting.Diagnostics[1]
[dataApi] Request starting HTTP/1.1 POST http://localhost:4280/graphql application/json 615
[dataApi] info: Microsoft.AspNetCore.Cors.Infrastructure.CorsService[4]
[dataApi] CORS policy execution successful.
[dataApi] info: Microsoft.AspNetCore.Routing.EndpointMiddleware[0]
[dataApi] Executing endpoint 'Hot Chocolate GraphQL Pipeline'
[dataApi] fail: Azure.DataApiBuilder.Core.Resolvers.IQueryExecutor[0]
[dataApi] 212add34-0c64-40dc-bd8a-047d7d69f916: The target table 'dbo.Building' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
[dataApi] fail: Azure.DataApiBuilder.Core.Resolvers.IQueryExecutor[0]
[dataApi] 212add34-0c64-40dc-bd8a-047d7d69f916: at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__208_0(Task`1 result)
[dataApi] at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
[dataApi] at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
[dataApi] --- End of stack trace from previous location ---
[dataApi] at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
[dataApi] --- End of stack trace from previous location ---
[dataApi] at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.ExecuteQueryAgainstDbAsync[TResult](TConnection conn, String sqltext, IDictionary`2 parameters, Func`3 dataReaderHandler, HttpContext httpContext, List`1 args) in /_/src/Core/Resolvers/QueryExecutor.cs:line 169
[dataApi] fail: Azure.DataApiBuilder.Service.Startup[0]
[dataApi] While processing your request the database ran into an error.
[dataApi] fail: Azure.DataApiBuilder.Service.Startup[0]
[dataApi] at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.ExecuteQueryAgainstDbAsync[TResult](TConnection conn, String sqltext, IDictionary`2 parameters, Func`3 dataReaderHandler, HttpContext httpContext, List`1 args) in /_/src/Core/Resolvers/QueryExecutor.cs:line 185
[dataApi] at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.<>c__DisplayClass19_0`1.<<ExecuteQueryAsync>b__0>d.MoveNext() in /_/src/Core/Resolvers/QueryExecutor.cs:line 124
[dataApi] --- End of stack trace from previous location ---
[dataApi] at Polly.Retry.AsyncRetryEngine.ImplementationAsync[TResult](Func`3 action, Context context, CancellationToken cancellationToken, ExceptionPredicates shouldRetryExceptionPredicates, ResultPredicates`1 shouldRetryResultPredicates, Func`5 onRetryAsync, Int32 permittedRetryCount, IEnumerable`1 sleepDurationsEnumerable, Func`4 sleepDurationProvider, Boolean continueOnCapturedContext)
[dataApi] at Polly.AsyncPolicy.ExecuteAsync[TResult](Func`3 action, Context context, CancellationToken cancellationToken, Boolean continueOnCapturedContext)
[dataApi] at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.ExecuteQueryAsync[TResult](String sqltext, IDictionary`2 parameters, Func`3 dataReaderHandler, HttpContext httpContext, List`1 args) in /_/src/Core/Resolvers/QueryExecutor.cs:line 125
[dataApi] at Azure.DataApiBuilder.Core.Resolvers.SqlMutationEngine.PerformMutationOperation(String entityName, EntityActionOperation operationType, IDictionary`2 parameters, IMiddlewareContext context) in /_/src/Core/Resolvers/SqlMutationEngine.cs:line 625
[dataApi] at Azure.DataApiBuilder.Core.Resolvers.SqlMutationEngine.ExecuteAsync(IMiddlewareContext context, IDictionary`2 parameters) in /_/src/Core/Resolvers/SqlMutationEngine.cs:line 127
[dataApi] at Azure.DataApiBuilder.Core.Services.ResolverMiddleware.InvokeAsync(IMiddlewareContext context) in /_/src/Core/Services/ResolverMiddleware.cs:line 142
[dataApi] at HotChocolate.Utilities.MiddlewareCompiler`1.ExpressionHelper.AwaitTaskHelper(Task task)
[dataApi] at HotChocolate.Execution.Processing.Tasks.ResolverTask.ExecuteResolverPipelineAsync(CancellationToken cancellationToken)
[dataApi] at HotChocolate.Execution.Processing.Tasks.ResolverTask.TryExecuteAsync(CancellationToken cancellationToken)
Code of Conduct
What happened?
Hey,
I have three tables User, Building and UserBuilding. I want to create a trigger that creates a userBuilding object once a building is created. However, once I run the following mutation:
which executes fine without the trigger, I get the error shown at the bottom. The gist ist
The target table 'dbo.Building' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.. I see the error should be addressed by #1630 but I'm still getting the error in v0.8.51.These are the sql schemas in case they are relevant:
CREATE TABLE [dbo].[Building] ( [id] INT IDENTITY (1, 1) NOT NULL, [street] NVARCHAR (50) NULL, [city] NVARCHAR (50) NULL, CONSTRAINT [PK_Building] PRIMARY KEY CLUSTERED ([id] ASC) ); CREATE TABLE [dbo].[User] ( [id] NVARCHAR (36) NOT NULL, [email] NVARCHAR (100) NOT NULL, [first_name] NVARCHAR (50) NULL, [last_name] NVARCHAR (50) NULL, [role] NVARCHAR (50) NULL, CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ([id] ASC) ) CREATE TABLE [dbo].[UserBuilding] ( [user_id] NVARCHAR(36) NOT NULL, [building_id] INT NOT NULL, [permission_id] INT DEFAULT 1 NOT NULL, PRIMARY KEY (user_id, building_id, permission_id), CONSTRAINT [FK_UserBuilding_User] FOREIGN KEY (user_id) REFERENCES [User](id), CONSTRAINT [FK_UserBuilding_Building] FOREIGN KEY (building_id) REFERENCES [Building](id), CONSTRAINT [FK_UserBuilding_Permission] FOREIGN KEY (permission_id) REFERENCES [Permission](id) );and the trigger
Version
0.8.51
What database are you using?
Azure SQL
What hosting model are you using?
Static Web Apps (SWA)
Which API approach are you accessing DAB through?
GraphQL
Relevant log output
Code of Conduct