分别写出以下触发器1.被参照表针对删除操作的参照完整性触发器2.被参照表针对修改操作的参照完整性触发器3.参照表针对插入操作的参照完整性触发器4.3.参照表针对修改操作的参照完整性触发器
时间: 2024-02-24 12:56:02 浏览: 16
1. 被参照表针对删除操作的参照完整性触发器:
```
CREATE TRIGGER trg_refIntegrityDelete
BEFORE DELETE ON ReferencedTable
FOR EACH ROW
BEGIN
IF EXISTS(SELECT * FROM ReferencingTable WHERE ReferencingColumn = OLD.ReferencedColumn) THEN
RAISE(ABORT, 'Cannot delete row from ReferencedTable as it is referenced in ReferencingTable.');
END IF;
END;
```
2. 被参照表针对修改操作的参照完整性触发器:
```
CREATE TRIGGER trg_refIntegrityUpdate
BEFORE UPDATE ON ReferencedTable
FOR EACH ROW
BEGIN
IF EXISTS(SELECT * FROM ReferencingTable WHERE ReferencingColumn = OLD.ReferencedColumn) THEN
RAISE(ABORT, 'Cannot update value in ReferencedTable as it is referenced in ReferencingTable.');
END IF;
END;
```
3. 参照表针对插入操作的参照完整性触发器:
```
CREATE TRIGGER trg_refIntegrityInsert
BEFORE INSERT ON ReferencingTable
FOR EACH ROW
BEGIN
IF NOT EXISTS(SELECT * FROM ReferencedTable WHERE ReferencedColumn = NEW.ReferencingColumn) THEN
RAISE(ABORT, 'Cannot insert row into ReferencingTable as it references a non-existent row in ReferencedTable.');
END IF;
END;
```
4. 参照表针对修改操作的参照完整性触发器:
```
CREATE TRIGGER trg_refIntegrityUpdate
BEFORE UPDATE ON ReferencingTable
FOR EACH ROW
BEGIN
IF NOT EXISTS(SELECT * FROM ReferencedTable WHERE ReferencedColumn = NEW.ReferencingColumn) THEN
RAISE(ABORT, 'Cannot update value in ReferencingTable as it references a non-existent row in ReferencedTable.');
END IF;
END;
```
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![application/msword](https://img-home.csdnimg.cn/images/20210720083327.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)