在INSERT TRIGGER之前创建，比较两个表之间的值

本文介绍了在INSERT TRIGGER之前创建，比较两个表之间的值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

由于我是TSQL的新手，因此需要创建插入前触发器的帮助。下面是两个表。

I need help creating a before insert trigger, as i am new to TSQL. below are the two tables.

SALARY表：

CREATE TABLE SALARY
(
    StarName varchar(30) NOT NULL,
    MovieTitle varchar(30)NOT NULL,
    MovieYearMade numeric(4, 0) NOT NULL,
    Amount numeric(8, 0) NULL,
    PRIMARY KEY (MovieTitle,StarName,MovieYearMade),
)

MOVIESTAR table

CREATE TABLE MOVIESTAR
(
    Name varchar(30) NOT NULL,
    Address varchar(20),
    City varchar(15) DEFAULT ('Palm Springs'), 
    Gender char(1) NULL CHECK (Gender ='M' OR GENDER ='F'),
    BirthYear Numeric(4),
    PRIMARY KEY CLUSTERED (Name)
)

我想创建一个触发器，以便在添加新电影时使用。如果 SALARY.MovieYearMade 在 MOVIESTAR.BirthYear SALARY.Amount 。 c $ c>。

I want to create a trigger so when a new movie is added. It prevents adding SALARY.Amount if SALARY.MovieYearMade is before MOVIESTAR.BirthYear.

当我比较两个表（即 SALARY 和 MOVIESTAR 。

I am confused as how to define trigger, when I am comparing values in two tables i.e. SALARY and MOVIESTAR.

谢谢，

`推荐答案`

您是否正在寻找类似的东西？

Are you looking for something like this?

CREATE TRIGGER tg_salary ON salary
INSTEAD OF INSERT AS
BEGIN
  INSERT INTO salary (StarName, MovieTitle, MovieYearMade, Amount)
  SELECT i.StarName, i.MovieTitle, i.MovieYearMade, 
         CASE WHEN i.MovieYearMade < s.BirthYear THEN NULL ELSE i.Amount END
    FROM INSERTED i JOIN moviestar s
      ON i.StarName = s.Name
END

这里是演示

Here is SQLFiddle demo

                        这篇关于在INSERT TRIGGER之前创建，比较两个表之间的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

Trigger