Sql Server, using OnDelete(DeleteBehavior.SetNull) on parent entity, where child have a trigger should not use OUTPUT #30206
Comments
pajacz
changed the title
|
Yes, should be.. |
|
Yeah... The above is generally by-design - you have to specify, on a table-by-table basis, whether the OUTPUT clause can be used or not; we don't automatically refrain from using OUTPUT on a dependent just because a principal has been configured as having a trigger. So the "workaround" above of calling HasTrigger on the dependent is the right way to do it (i.e. isn't a workaround). |
|
@roji The point here is that the only entity being saved (Office) doesn't have a trigger in the database. The trigger is on Employee, which is not being saved. But this doesn't work because a cascading update is defined from Office to Employee. So, the workaround, and it is a workaround, is to tell EF that Office has a trigger when it doesn't. Full repro below: public class Office
{
public int Id { get; set; }
public List<Employee> Employees { get; } = new();
}
public class Employee
{
public int Id { get; set; }
public Office? Office {get; set;}
public int? OfficeId {get; set;}
public int Foo {get; set;}
}
public class Program
{
public static async Task Main()
{
using (var context = new SomeDbContext())
{
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();
await context.Database.ExecuteSqlRawAsync(
@"
CREATE TRIGGER TRG_InsertUpdateBlog
ON Employee
AFTER INSERT, UPDATE AS
BEGIN
IF @@ROWCOUNT = 0
return
SET nocount on;
UPDATE Employee set Foo = Foo + 1
WHERE Id IN(SELECT INSERTED.Id FROM INSERTED);
END");
context.Add(new Office { Employees = { new() } });
await context.SaveChangesAsync();
}
using (var context = new SomeDbContext())
{
var record = context.Find<Office>(1)!;
context.Remove(record);
await context.SaveChangesAsync();
}
}
}
public class SomeDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow")
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Office>()
.HasMany(_ => _.Employees)
.WithOne(_ => _.Office)
.OnDelete(DeleteBehavior.SetNull);
modelBuilder.Entity<Employee>()
.ToTable(_ => _.HasTrigger("UpdateSomething"));
}
}Output: |
|
Msybe the new update pipeline should disable itself if any triggers are defined on any table? |
|
Seems similar to #30253 |
Thanks for clarying that, I missed that point.
Cascading delete in this sample, right? Just to be sure I understand. In any case yeah, this is unfortunate and I agree the above is a workaround. We could look into adding logic for detecting if there are any dependents with cascading delete, but that's starting to be a lot.
We could, but I'm not sure I'd want to go that far... |
No, this is a cascading update: CONSTRAINT [FK_Employee_Office_OfficeId] FOREIGN KEY ([OfficeId]) REFERENCES [Office] ([Id]) ON DELETE SET NULLCascade delete does not have this problem. |
|
Note from triage: Covered by #29916, or by adding a dummy trigger on the parent table if turning off the behavior globally is not desired. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
File a bug
If a parent entity Office has OnDelete(DeleteBehavior.SetNull), with collection of Employee entity, where Employee has a trigger defined. Delete will cause "Error: Cannot continue the execution because the session is in the kill state.", caused by DELETE .. OUTPUT 1 ... instead of SELECT @@rowcount;
Basically issue is, when an entity has SetNull on delete and is linked to an entity with a trigger, you should avoid using OUTPUT. I guess :)
There is a workaround, you have to "set" non existing trigger to Office as well.
Include your code
Configuration:
test:
Include stack traces
Include provider and version information
EF Core version:
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0.2, EF 7.0.2
Operating system: MacOS 13.1 (22C65)
IDE: JetBrains Rider 2022.3.1
The text was updated successfully, but these errors were encountered: