Oracle触发器插入/更新 [英] Oracle Trigger Insert/update

问题描述

好，所以我正在做作业. 我有三个表，电影(标题，年份，长度，类型，工作室名称，制片人)，StarsIn(电影标题，starName)，电影明星(名称，地址，性别，生日)

Ok so i am working on a homework assignment. I have three tables, Movies (title, year, length, genre, studioName, producer) StarsIn (movieTitle, starName) MovieStar (name, address, gender, birthdate)

基本上，我需要确定何时有人在Stars中插入或更新了某些内容，这些更改也需要在MovieStar中发生.

Basically i need to make sure when someone inserts or updates something in StarsIn those changes need to happen in MovieStar also.

到目前为止，我有这样的事情.

So far i have something like this..

CREATE OR REPLACE TRIGGER testTrig
AFTER UPDATE OR INSERT ON STARSIN
DECLARE
l_name MOVIESTAR.NAME%TYPE;
BEGIN
SELECT NAME FROM MOVIESTAR INTO l_name;
FOR EACH ROW WHEN (new.STARSIN.STARNAME NOT IN l_name)
INSERT INTO MOVIESTAR(NAME) VALUES (new.STARSIN.STARNAME);
END;

我遇到一些编译器错误

Error(4,1): PL/SQL: SQL Statement ignored
Error(4,28): PL/SQL: ORA-00933: SQL command not properly ended
Error(5,10): PLS-00103: Encountered the symbol "ROW" when expecting one of 
the following: in

我对oracle非常陌生，并且正在练习Triggers.我知道使用外键很容易做到这一点，但是分配是使用触发器.

I am very new to oracle and I am practicing Triggers. I know that this could be easily done using foreign keys, but the assignment is to use triggers.

我真的可以为此提供一些帮助.我已经尝试了上百万种不同的方法来实现这一目标，但是没有运气.

I could really use some help with this. I have tried like a million different ways to make this happen, with no luck.

在此先感谢您的帮助/建议.

Thanks in advance for any help/advice.

推荐答案

我建议为每行指定触发条件.我发现这些要容易得多.

I'd suggest specifying the trigger fire for each row. I find these a lot easier.

您可以进行计数以查看MovieStar.Name值是否已经存在，然后插入(如果不存在)；这与您上面的方法类似.如果另一个用户在您检查的时间和插入的时间之间插入了电影明星，这将失败，但是对于类分配而言，这可能就足够了.有公认的不失败方法，但是您可能尚未在课堂上介绍过.

You can do a count to see if the MovieStar.Name value already exists and then insert if it doesn't; that's similar to the approach you have above. This will fail if another user inserts the movie star between the time you check and the time you insert, but it's probably good enough for a class assignment. There are accepted no-fail approaches for this but you may not have covered them in class yet.

尝试这样的事情；它可能包含了您到目前为止在课堂上介绍的所有内容:

Try something like this; it probably incorporates everything you've covered in class so far:

CREATE OR REPLACE TRIGGER TestTrig
AFTER UPDATE OR INSERT ON STARSIN
FOR EACH ROW
DECLARE
  movieStarCount NUMBER;
BEGIN
  SELECT COUNT(*) INTO movieStarCount
    FROM MovieStar
    WHERE Name = :NEW.StarName;
  IF movieStarCount = 0 THEN
    INSERT INTO MovieStar (Name) VALUES (:NEW.StarName);
  END IF;
END;

这篇关于Oracle触发器插入/更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！