触发器内的循环和条件
我想将这段逻辑作为触发器来实现,但我不知道该怎么做!我想创建一个触发器,当删除一行时,它会检查其某一列的值是否存在于另一个表中,如果存在,它还应该根据另一列对另一个表执行删除。
假设我们有一个表 Foo,其中包含 Bar、Baz 列。如果我不使用触发器,这就是我要做的事情:
function deleteFromFooTable(FooId)
{
SELECT (Bar,Baz) FROM FooTable WHERE id=FooId
if not-empty(SELECT * FROM BazTable WHERE id=BazId)
DELETE FROM BarTable WHERE id=BarId
DELETE FROM FooTable WHERE id=FooId
}
我在伪代码中跳了一些圈子,但我希望你们都能明白我要去的地方。看来我需要的是一种在触发器语句中执行条件和循环(在多行删除的情况下?)的方法。到目前为止,我还没有找到任何东西。这是不可能的,还是这种做法不好?谢谢!
I have this piece of logic I would like to implement as a trigger, but I have no idea how to do it! I want to create a trigger that, when a row is deleted, it checks to see if the value of one of its columns exists in another table, and if it does, it should also perform a delete on another table based on another column.
So say we had a table Foo that has columns Bar, Baz. This is what id be doing if i did not use a trigger:
function deleteFromFooTable(FooId)
{
SELECT (Bar,Baz) FROM FooTable WHERE id=FooId
if not-empty(SELECT * FROM BazTable WHERE id=BazId)
DELETE FROM BarTable WHERE id=BarId
DELETE FROM FooTable WHERE id=FooId
}
I jumped some hoops in that pseudo code, but i hope you all get where im going. It seems what i would need is a way to do conditionals and to loop(in case of multiple row deletes?) in the trigger statement. So far, I haven't been able to find anything. Is this not possible, or is this bad practice? Thanks!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
如果您没有在这些表之间设置外键关系,则需要这样做。我将向您展示创建表时用于设置所需内容的命令。显然,您只需要更新表即可。
当删除 Foo 记录时,这将删除所有“孤立”记录。
If you do not have Foreign Key relationships setup between these tables, you need to do that. I will show you the command for setting up what you want when you create the table. Clearly, you will just need to update the table.
This will delete any "orphans" when a Foo record is deleted.