So I have two tables
CREATE TABLE dbo.Orders
order_id int NOT NULL IDENTITY(1,1) CONSTRAINT PK_ORDERS PRIMARY KEY CLUSTERED,
order_description varchar(250) NOT NULL
CREATE TABLE dbo.Invoices
invoice int NOT NULL CONSTRAINT PK_INVOICES PRIMARY KEY CLUSTERED,
sales_amount decimal(15,4) NOT NULL CONSTRAINT DF_INVOICES_salesamount DEFAULT (0.00),
order_id int NULL CONSTRAINT FK_INVOICES_ORDERS FOREIGN KEY REFERENCES dbo.Orders(order_id) ON DELETE SET NULL,
deleted bit NOT NULL CONSTRAINT DF_INVOICES_deleted DEFAULT (1)
Each table also has a couple of after triggers for insert and update, the invoices table has an instead of delete trigger, and there's an indexed view that references both tables.
The problem is that the "ON DELETE SET NULL" doesn't seem to work. If I test it, it behaves as though "No action" is the behavior, and if I do an sp_help, the results there also suggest "No action" but if I look in sys.foreign_keys, the delete_referential_action is set to SET_NULL.
I'm not sure what's causing the behavior or if the above is enough to go by. So far, I haven't had much luck looking through BOL; was hoping someone might have some insight as to where I could confirm why the behavior is happening.