sql触发器将行插入两个表
我正在寻找一种创建触发器的方法,该触发器会将同一行插入具有相同值的两个表中。
例如,插入新行后立即将其插入到pushNotificationQueue中,我希望将同一行插入到消息中。
我尝试了这个
CREATE TRIGGER add_to_messages
after insert on mbb_pushNotificationQueue
FOR EACH ROW
insert into mbb_messages select * from mbb_pushNotificationQueue
,唯一的问题是它会遍历并添加之前已添加的条目。
I was looking for a way to create a trigger that would insert the same row into two tables with the same values.
For example, a new row is inserted into pushNotificationQueue as soon as that is inserted, I would like that same exact row to be inserted into messages.
I tried this
CREATE TRIGGER add_to_messages
after insert on mbb_pushNotificationQueue
FOR EACH ROW
insert into mbb_messages select * from mbb_pushNotificationQueue
the only problem with that is that it goes through and adds entries that have already been previously added.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
所以只添加最后添加的记录。
有 max(id) 之类的。
So only add the last record added.
Having max(id) or something.
您必须说明您正在使用哪个 RDBMS。
无论如何,您必须使用一个通常命名为 insert 或类似名称的特殊表。
这是针对 Sql Server 的:
Sybase 等其他服务器使用 REFERENCES 子句来获取新插入的记录:
对于 MySQL(您似乎正在使用),您可以使用 NEW 表来引用新插入的记录:
You have to say with which rdbms you are working.
Anyway, you have to use a special table normally named inserted or similar.
This is for Sql Server:
Others like Sybase use a REFERENCES clause to get to the newly inserted record:
And for MySQL (which you are seem to use) you can refer to the newly inserted records by using the NEW table:
您需要将列名与 new 关键字一起使用。请在下面找到触发器:
You need to use the column name with new keyword. Please find the trigger below:
首先,我要说的是,将
select *
与insert-select
语句一起使用确实是一个坏主意。原因是您永远无法预测从选择中返回的列的顺序。其次,假设使用 SQL Server,我建议使用以下内容:
免责声明:
First of all, I say that using
select *
with aninsert-select
statement is really, really a bad idea. The reason is that you can never predict the order of the columns that are returned from a selection.Secondly, assuming SQL Server, I would suggest using the following:
Disclaimer:
我最终使用了
感谢所有发帖的人。
I ended up using
Thanks to everyone who posted.