Click here to Skip to main content
15,885,366 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
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

-- Excut
--------------------------------------------------------------------------
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)-- how to do this here there is wrong NOT to duplicate
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
------------------------------------------------------------------------
Posted
Updated 25-Nov-15 14:01pm
v2
Comments
PIEBALDconsult 25-Nov-15 20:00pm    
Please don't use triggers.
jgakenhe 25-Nov-15 23:59pm    
Do everything in updates and inserts, don't mess with triggers. Putting this business logic in the database will create a mess and won't be maintainable.

1 solution

Hi Maher,

Use following trigger:

SQL
create TRIGGER [dbo].[TRG_TAble1]
   ON  [dbo].[Table1] 
   AFTER update
AS 
BEGIN
	DECLARE @ID INT
	DECLARE @OldValue INT
	DECLARE @NewValue INT
	DECLARE @clmA VARCHAR(50)
	select @OldValue=clmB from deleted
	select @ID=ID,@clmA=clmA,@NewValue=clmB from inserted
	
	IF @NewValue>0
	BEGIN
		UPDATE Table1 SET clmA=@clmA, clmB = (clmB + @NewValue+@OldValue) 
		WHERE ID = @ID
	END
	ELSE
	IF @NewValue<0
	BEGIN
		UPDATE Table2 SET clmA=@clmA, clmB = (clmB + @NewValue+@OldValue) 
		WHERE ID = @ID
	END
  
END
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900