如何使用触发器更新 [英] How to Update Over using Triggers

查看:79
本文介绍了如何使用触发器更新的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

  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 = 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆