sql server - SQL trigger to delete from another table -

i have 2 tables:

products:

product id | quantity 

orderslines:

product id | amount --(multiple lines same id) 

naturally, before delete product products table, need delete order lines product id first orderslines table.

so wrote trigger it:

create trigger [dbo].[atbl_sales_products_dtrig] on [dbo].[atbl_sales_products] delete begin     delete atbl_sales_orderslines     productid = (select productid deleted) end 

however, when try delete in form products table, still says:

there rows in related table (sales orderslines) must removed before row can deleted

what doing wrong here?

edit per request: main constraint:

[dbo].[atbl_sales_orderslines]  check add  constraint  [fk_atbl_sales_orderslines_atbl_sales_products] foreign key([productid]) references [dbo].[atbl_sales_products] ([productid]) 

the main problem in case trigger created for delete trigger. it's fired after delete operation - cause error.

you should create instead of delete trigger , solve problem.

second problem shouldn't use = in subquery bacause can return more 1 row. should use in operator.

you can see example below cause error both problems.

insert products(productid, quantity) select 1,1 union select 2,2 go  insert orderslines(productid,amount) select 1,2 union select 1,3 union select 2,4 union select 2,5 go  delete products go 

this pass when trigger fine.