为什么 SQL Server CE 不允许触发此触发器? [英] Why does SQL Server CE not allow this trigger to fire?

问题描述

我们使用的是 SQL Server CE 数据库.我了解使用 SQL Server CE 数据库的触发器类型存在限制.例如，SQL Server CE 表上的触发器不能调用存储过程，但它可以更新/插入另一个表.

We're using a SQL Server CE database. I understand that there are limitations to the types of trigger that work with a SQL Server CE database. For instance, the trigger on a SQL Server CE table cannot call a stored procedure, but it can update / insert into another table.

我们想要的是一个触发器，当对表进行修改(插入/更新/删除)时发送电子邮件通知.我们知道，如果我们直接在表上设置触发器来发送电子邮件(EXEC msdb.dbo.sp_send_dbmail)，当您尝试推送到表时会出现错误.显然，SQL Server CE 不支持触​​发存储过程的跟踪表上的触发器.

What we are after is a trigger to send an email notification when there is a modification (insert/update/delete) to a table. We know that if we set the trigger directly on the table to send an email (EXEC msdb.dbo.sp_send_dbmail) that you will get an error when you attempt to push to the table. Clearly SQL Server CE does not support a trigger on a tracked table which fires a stored procedure.

为了解决这个问题，我们在表上设置了一个触发器，将一行插入到另一个表 (_table_changes) 中，该表不会作为 CE 同步的一部分进行跟踪.在这个表 (_table_changes) 上，我们设置了一个触发器来发送电子邮件通知，我们希望它能够解决跟踪的 CE 表触发器无法触发存储过程，进而无法触发电子邮件的问题.

To get around this we set a trigger on the table, to insert a row into another table (_table_changes), which is not tracked as part of CE synchronisation. On this table (_table_changes) we set a trigger to send the email notification, which we hoped would get around the problem with a tracked CE table trigger not being able to fire a stored procedure, and in turn an email.

然而，即使该表不是推/拉同步的一部分，如果我们在其上启用电子邮件触发器同步将不起作用.为什么是这样?CE 怎么可能知道一个不属于同步的表上有一个触发器，它会触发一个存储过程来发送电子邮件?

However, even though the table is not part of push/pull synchronisation, if we enable the email trigger on it synchronisation will not work. Why is this? How can CE even be aware that a table, which is not part of synchronisation, has a trigger on it which fires a stored procedure to send an email?

推荐答案

我认为是否将表作为 CE 同步的一部分进行跟踪并不重要.

I don't think it matters whether the table is tracked as part of CE synchronisation or not.

SQL Server Compact Edition 4.0 不支持触​​发器

Triggers are not supported in SQL Server Compact Edition 4.0

SQL Server Compact 和 SQL Server 之间的差异

编辑

根据这篇 MSDN 文章 SQL Server CE 中的 RDA 允许在 RDA 从中提取数据的 SQL Server 表上触发.它还指出，对于跟踪表，可以在将更改推送回 SQL Server 时执行 SQL Server 触发器.

According to this MSDN article RDA in SQL Server CE allows triggers on a SQL Server table that RDA pulls data from. It also states that for tracked tables SQL Server triggers can be executed when changes are pushed back to SQL Server.

触发器.SQL Server Compact Edition 不支持触​​发器.但是，触发器可以驻留在 RDA 拉取的 SQL Server 表上数据来自.对于跟踪表，可以执行 SQL Server 触发器当更改被推送回 SQL Server 时.您可能必须指定在触发逻辑中设置 NOCOUNT ON.这表示不返回受影响的行数，因为响应不受影响的行"导致 RDA Push 方法出错.

Triggers. SQL Server Compact Edition does not support triggers. However, triggers can reside on the SQL Server table that RDA pulls data from. For tracked tables, the SQL Server triggers can be executed when changes are pushed back to SQL Server. You might have to specify SET NOCOUNT ON in the trigger logic. This indicates not to return the number of rows affected, because a response of "no rows affected" causes an error for the RDA Push method.

我会检查您是否在触发器中使用了 SET NOCOUNT ON.

I would check that you are using SET NOCOUNT ON within your trigger.

说了这么多，如上所述，不支持在 SQL Server CE 中使用触发器.

Having said all this, and as mentioned above, the use of triggers in SQL Server CE are not supported.

也许更好的解决方案是创建需要发送的电子邮件的队列表.当这些数据从 SQL CE 推送回您的 SQL Server 时，您可以安排一个 SQL 代理作业来检查队列表并以这种方式发送邮件.

Maybe a better solution would be to create a queue table of emails that are required to be sent. When this data is pushed from SQL CE back to your SQL Server you could schedule a SQL Agent job to check the queue table and send mail that way.

这篇关于为什么 SQL Server CE 不允许触发此触发器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！