【SQL】约束与触发器2

3.修改约束

3.1给约束命名

按如下格式命名：

name CHAR(30) CONSTRAINT NameIsKey PRIMARY KEY

gender CHAR(1) CONSTRAINT NoAndro
                            CHECK (gender IN ('F','M'))

CONSTRAINT RightTitle
    CHECK (gender = 'F' or name NOT LIKE 'Ms.%')

3.2 修改表上的约束

①修改约束的检查时间

SET CONSTRAINT MyConstraint DEFERRED;

SET CONSTRAINT MyConstraint IMMEDIATE;

②用ALTER TABLE语句

删除约束 DROP

ALTER TABLE MovieStar DROP CONSTRAINT NameIsKey;
ALTER TABLE MovieStar DROP CONSTRAINT NoAndro;
ALTER TABLE MovieStar DROP CONSTRAINT RightTitle;

添加约束 ADD（注意，约束必须是要整个表遵守时才加）

ALTER TABLE MovieStar ADD CONSTRAINT NameIsKey
    PRIMARY KEY(name);
ALTER TABLE MovieStar ADD CONSTRAINT NoAndro
    CHECK (gender IN ('F','M'));
ALTER TABLE MovieStar ADD CONSTRAINT RightTitle
    CHECK (gender = 'F' OR name NOT LIKE 'Ms.%');

注意：SQL不会记住已经删除的约束，所以如果要添加之前删除的约束必须重新写一遍。

四、断言

1.创建断言

CREATE ASSERTION 断言名 CHECK (条件)

2.使用断言

CREATE ASSERTION RichPres CHECK
    (NOT EXISTS
        (SELECT Studio.name
         FROM Studio, MovieExec
         WHERE presC# = cert# AND netWorth < 1000000
        )
    );

上面的断言不允许资金小于1000000的经理。

CREATE ASSERTION SumLength CHECK (10000 >= ALL
    (SELECT SUM(length) FROM Movies GROUP BY studioName)
);

上面的断言不允许电影公司的电影总长度超过10000分钟。

如果这个语句用CHECK约束写，是

CHECK ( 10000 >= ALL
    (SELECT SUM(length) FROM Movies GROUP BY studioName));

断言和CHECK的区别：

CHECK只在插入和修改时有效，但对删除不做检查，在有些情况下会导致约束不成立。

断言则在任何提及的关系改变时都会检查，确保条件成立。

3.删除断言

DROP ASSERTION 断言名

五、触发器

当特定事件发生时，触发器被激活。如果状态满足触发器中的条件，则该触发器相连的动作被执行。

例子：

1.不允许降低电影制作人的净资产

CREATE TRIGGER NetWorthTrigger
AFTER UPDATE OF netWorth ON MovieExec
REFERENCING
    OLD ROW AS OldTuple,
    NEW ROW AS NewTuple
FOR EACH ROW
WHEN (OldTuple.netWorth > NewTuple.netWorth)
    UPDATE MovieExec
    SET netWorth = OldTuple.netWorth
    WHERE cert# = NewTuple.cert#;

2.阻止电影制作人的平均资产降到500000.要对插入、删除、修改分别设置触发器。这里列出修改的：

CREATE TRIGGER AvgNetWorthTrigger
AFTER UPDATE OF netWorth ON MovieExec
REFERENCING
    OLD TABLE AS OldStuff,
    NEW TABLE AS NewStuff
FOR EACH STATEMENT
WHEN (500000 > (SELECT AVG(netWorth) FROM MovieExec))
BEGIN
    DELETE FROM MovieExec
    WHERE (name, address, cert#, netWorth) IN NewStuff;
    INSERT INTO MovieExec
        (SELECT * FROM OldStuff);
END;

3.以year为主键的一部分，防止主键为空。在插入前如果发现year为空则插入默认值1915

CREATE TRIGGER FixYearTrigger
BEFORE INSERT ON Movies
REFERENCING
    NEW ROW AS NewRow
    NEW TABLE AS NewStuff
FOR EACH ROW
WHEN NewRow.year IS NULL
UPDATE NewStuff SET year = 1915;