CREATE TABLE Table1
(
ID int IDENTITY(1,1),
clmA varchar(50) NULL,
clmB int,
PRIMARY KEY (ID)
)
Insert into Table1 values('A',5)
Insert into Table1 values('B',2)
CREATE TABLE Table2
(
ID int,
clmA varchar(50) NULL,
clmB int
)
CREATE PROCEDURE dbo.ProcTable1
(
@clmA VARCHAR(50),
@clmB INT
)
AS
BEGIN
DECLARE @ID INT
UPDATE Table1 SET clmA=@clmA, clmB = (clmB + 3)
WHERE ID = @ID
END
BEGIN
DECLARE @ID INT
UPDATE Table2 SET clmA=@clmA, clmB = (clmB - 3)
WHERE ID = @ID
END
CREATE TRIGGER TrG ON dbo.Table1
FOR INSERT
AS
DECLARE @ID int
DECLARE @clmA varchar(50)
DECLARE @clmB int
SELECT
@ID = i.ID,
@clmA = i.clmA,
@clmB = i.clmB
FROM inserted i;
INSERT INTO Table2(ID, clmA, clmB)
VALUES (@ID, @clmA, clmB + @clmB);
CREATE TRIGGER TrgN ON dbo.Table1
FOR UPDATE
AS
DECLARE @ID int
DECLARE @clmA varchar(50)
DECLARE @clmB int
SELECT
@ID = i.ID,
@clmA = i.clmA,
@clmB = i.clmB
FROM inserted i;
UPDATE Table3
SET
ID = @ID,
clmA = @clmA,
clmB = (clmB - @clmB)
WHERE ID = @ID
---------------------------------------------------------------------
-- The question is? I need the trigger to tell in table2 the value has been add.. Not to tell the value has been updated in the row in table1.
-- example: Insert into Table1 values( 'A' , 5 )
-- if we Update this value in Table1 the column2 has value 5 to be +3 Over 5.. I mean 3 plus 5 = 8 .. I need the trigger to tell in table2.' . 3 . is added.. but in new row.. Not 8 in the same row with duplicate. also if 5 - 3 = 2 this will insert with update to table 3..
-- Like this
Table 1
-- ('A', 5) Old row
Table 2
-- ('A', 3) New Row .. this row should fire by Trigger for INSERT and UPDATE
-----------------
Table 3
-- ('A', 2) New Row .. this row should fire by Trigger for INSERT and UPDATE
------------------------------------------------------------------------