在 SQL Server 中创建触发器
当我想使用 SQL Server 2008 R2 的预定义“CREATE TRIGGER”创建触发器时,我迷失了方向。您能否给我一个直接的 SQL 语句,我可以用它来创建触发器,并告诉我如何定义 AFTER、BEFORE 等?
另外,我如何知道行已更新/已插入/已删除,并使用它们的列值在触发器内执行操作?
I got lost when I wanted to create trigger using the pre-defined "CREATE TRIGGER" of SQL Server 2008 R2. Could you please give me a direct SQL statement that I can use to create a trigger, and tell me how to define AFTER, BEFORE, and all that?
Also, how can I know the rows UPDATED/INSERTED/DELETED, and use their column values to do operations inside the trigger?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
基本语法是
The basic syntax is
数据库是面向集合的,触发器也不例外。当执行给定操作并且该操作可能影响多行时,触发器将触发。因此,问题“假设我想知道该行的主键”是用词不当。可以插入多行。
SQL Server 为 AFTER 触发器提供了两个名为
inserted
和deleted
的特殊表,它们表示由操作插入或删除的行,其结构与受影响的表相同。更新触发器可能会填充inserted
和deleted
,而插入触发器只会填充inserted
表。来自评论:
这个问题的答案是使用
inserted
表(同样,您必须假设可以有多行)来循环显示行并发送电子邮件。但是,我建议不要将电子邮件逻辑放入触发器中,而是建议将该逻辑放入存储过程中,然后。从中发送您的电子邮件以供参考:创建触发器
Databases are set-oriented and triggers are no different. A trigger will fire when a given operation is performed and that operation might affect multiple rows. Thus, the question
"Say I want to know the Primary Key of that row"
is a misnomer. There could be multiple rows inserted.SQL Server provides two special tables for AFTER triggers named
inserted
anddeleted
which represent the rows that were inserted or deleted by an action and are structured identically to the table being affected. An update trigger might populate bothinserted
anddeleted
whereas an insert trigger would only populate theinserted
table.From comments:
The answer to this question is to use the
inserted
table (which again, you must assume could have multiple rows) to cycle through the rows and send an email. However, I would recommend against putting email logic in a trigger. Instead, I would recommend putting that logic in a stored procedure and send your email from that.For reference: Create Trigger
触发器是一个基于事件的过程,在表以某种方式更改后“触发”。这将发生在删除、更新、插入等操作上。 BEFORE 和 AFTER 语法将定义是在提交事件之前还是之后运行触发器。
这是简短的版本。请查看 MSDN。
A trigger is an event-based process that is "triggered" after a table is changed in some way. This will be on DELETE, UPDATE, INSERT, and so forth. Your BEFORE and AFTER syntax will define whether to run the trigger before or after the event is committed.
That's the short version. Check out MSDN.