Hi,
I have two tables as shown in below,
Table1
ID (INT, PK)
Param1 (NVARCHAR(50))
Table2
ID (INT, PK)
t1_ID (INT, FK)
Table1 is connected to Table2 via Table1.ID-Table2.t1_ID. And relationship has cascade at delete.
What I want to do is when user deletes a row in Table1 all associated rows in Table2 also deletes(which is why I open the cascade at delete), but I also want that when the rows deleting in Table2 get a Parameter at Table1.
How can I do that?
Thanks in advance.
I think I need to be more specific of what I'm trying to accomplish.
Think about 3 tables. Like this;
Tables
When user deletes a payment in Payments table, the amounts in PaymentItems table associated with PaymentID will be added or substracted in Balance column in CompanyFinanceInformations table by PaymentType.
What I have tried:
CREATE TRIGGER DeleteTrigger
ON dbo.Table2
FOR DELETE
AS
BEGIN
DECLARE @Param1 AS NVARCHAR(50)
DECLARE @t1_ID AS INT
SELECT @t1_ID = t1_ID FROM deleted
SELECT @Param1 = Param1 FROM dbo.Table1 WHERE dbo.Table1.ID = @t1_ID
END
I think about write a trigger for Table1, and in this trigger loop through all asscociated rows on Table2. But I have concerns about the performance and efficiency of this proccess.