如何使用Sql Trigger更改表中的值？ [英] How Do I Change The Value In Table Using Sql Trigger?

问题描述

我正在尝试编写SQL触发器，根据以下价格图表更改插入数据库的每部新电影的rental_rate。



特殊功能'预告片10c增加特色'评论'50c增加特色'删除场景'20c增加特色'幕后花絮'20c减少



这就是我所拥有的远：

创建或替换TRIGER CHArraa 
 AFTER 
 INSERT ON film 
 DECLARE special_features varchar2（50 ）; 
 rental_rate数字; 
 IF（special_features ='预告片'）那么
 rental_rate：= rental_rate + 0.10; 
 IF（special_features ='Commentaries'）THEN 
 rental_rate：= rental_rate + 0.50; 
 IF（special_features ='已删除的场景'）那么
 rental_rate：= rental_rate + 0.20; 
 IF（special_features ='Behind the Scenes'）那么
 rental_rate：= rental_rate  -  0.20; 
 END IF; 
 END IF; 
 END IF; 
 END IF; 
 END; 

我可以得到一些帮助修复并完成这项工作

解决方案

创建INSTEAD OF INSERT触发器。这是脚本

  CREATE   TRIGGER  CHArraa  ON 电影
 INSTEAD  OF   INSERT  
  AS  
  BEGIN  
  SET   NOCOUNT   ON  
  INSERT   INTO  dbo.film 
  SELECT  
 special_features，
  CASE   WHEN  special_features = ' 预告片 那么 rental_rate +  0 。 10  
  WHEN  special_features = '  Commentaries' 那么 rental_rate +  0 。 50  
  WHEN  special_features = ' 已删除的场景 那么 rental_rate +  0 。 20  
  WHEN  special_features = ' 幕后的 那么 rental_rate  -   0 。 20  
  END   AS  rental_rate 
  FROM  INSERTED 
  END  

I am trying to write SQL trigger to change the rental_rate of every new film inserted into the database on the basis of the following price chart.

special feature 'trailers' 10c increase special feature 'commentaries' 50c increase special feature 'Deleted Scenes' 20c increase special feature 'Behind the Scenes' 20c decrease

This is what I have so far:

CREATE OR REPLACE TRIGGER CHArraa
AFTER
INSERT ON film
DECLARE special_features varchar2(50);
rental_rate number;
IF (special_features = 'Trailers') THEN
           rental_rate := rental_rate + 0.10;
IF (special_features = 'Commentaries') THEN
           rental_rate:= rental_rate+ 0.50;
IF (special_features = 'Deleted Scenes') THEN
           rental_rate := rental_rate + 0.20;
IF (special_features = 'Behind the Scenes') THEN
          rental_rate := rental_rate - 0.20;
END IF;
END IF;
END IF;
END IF;
END;

Can I get some get help to fix and make this work

解决方案

Create INSTEAD OF INSERT trigger. Here is script

CREATE TRIGGER CHArraa ON film
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
    INSERT INTO dbo.film
    SELECT 
      special_features,
	CASE WHEN special_features = 'Trailers' THEN rental_rate + 0.10 
             WHEN special_features = 'Commentaries' THEN rental_rate+ 0.50
     	     WHEN special_features = 'Deleted Scenes' THEN rental_rate + 0.20
    	     WHEN special_features = 'Behind the Scenes' THEN rental_rate - 0.20
	END  AS rental_rate
    FROM INSERTED
END

这篇关于如何使用Sql Trigger更改表中的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！