如何使用触发器更新 [英] How to Update Over using Triggers
问题描述
CREATE TABLE 表1
(
ID int IDENTITY ( 1 ,< span class =code-digit> 1 ),
clmA varchar ( 50 ) NULL ,
clmB int ,
PRIMARY KEY (ID)
)
将 插入表1 值(' A', 5 )
Insert into 表1 值(' B', 2 )
- ------ ----------------------------------
CREATE 表表2
(
ID int ,
clmA varchar ( 50 ) NULL ,
clmB int
)
- ------------------------------------------- ----------------------------
创建 PROCEDURE dbo.ProcTable1
(
@ clmA VARCHAR ( 50 ),
@ clmB INT
)
AS
BEGIN
DECLARE @ID INT
更新表1 SET clmA = @ clmA,clmB =(clmB + 3 )
WHERE ID = @ ID
END
BEGIN
DECLARE @ ID INT
更新表2 SET clmA = @ clmA,clmB = (clmB - 3 )
WHERE ID = @ID
END
- < span class =code-评论> Excut
- ------------ -------------------------------------------------- ----------
创建 TRIGGER TrG < span class =code-keyword> ON dbo.Table1
FOR INSERT
AS
DECLARE @ ID int
DECLARE @ clmA varchar ( 50 )
DECLARE @ clmB int
< span class =code-keyword> SELECT
@ ID = i.ID,
@ clmA = i.clmA,
@ clmB = i.clmB
FROM 插入i;
INSERT INTO 表2(ID,clmA,clmB)
VALUES ( @ ID , @ clmA , clmB + @ clmB );
- -------------------- -------------------------------------------------- ------
创建 TRIGGER TrgN ON dbo.Table1
FOR 更新
AS
DECLARE @ ID int
DECLARE @ clmA varchar ( 50 )
DECLARE @ clmB int
SELECT
@ ID = i.ID,
@ CLMA 温泉n> = i.clmA,
@ clmB = i.clmB
FROM 插入i;
更新表3
SET
ID = < span class =code-sdkkeyword> @ ID ,
clmA = @ clmA ,
clmB =(clmB - @ clmB ) - 这里怎么做有错误NOT复制
WHERE ID = @ ID
------------------------------------------ ---------------------------
- 问题是?我需要触发器在table2中告诉值已添加..不要告诉值已在table1的行中更新。
- 示例:插入Table1值('A', 5)
- 如果我们在Table1中更新这个值,则column2的值为5为+3超过5 ..我的意思是3加5 = 8 ..我需要触发器告诉table2。 '。 3。添加..但在新行..不是8在同一行有重复。如果5 - 3 = 2,这将插入更新到表3 ..
- 像这样
表1
- ( 'A',5)旧行
表2
- ('A',3)新行..此行应该通过触发器激活INSERT和UPDATE
-----------------
表3
- ('A',2)New Row ..此行应该由Trigger触发INSERT和UPDATE
------------------- -------------------------------------------------- ---
Hi Maher,
使用以下触发器:
创建 TRIGGER [dbo] 。[TRG_TAble1]
ON [dbo]。[表1]
AFTER update
AS
BEGIN
DECLARE @ ID INT
DECLARE @ OldValue INT
DECLARE @ NewValue INT
DECLARE @ clmA VARCHAR ( 50 )
选择 @ OldValue = clmB 来自已删除
选择 @ ID = ID,@ clmA = clmA,@ NewValue = clmB 来自插入
IF @ NewValue> 0
BEGIN
更新表1 SET clmA = @ clmA,clmB =(clmB + @ NewValue + @ OldValue)
WHERE ID = @ ID
END
ELSE
IF @ NewValue< 0
BEGIN
UPDATE 表2 SET clmA = @clmA,clmB =(clmB + @NewValue + @ OldValue)
WHERE ID = @ ID
END
END
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
------------------------------------------------------------------------
Hi Maher,
Use following trigger:
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
这篇关于如何使用触发器更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!