如何在 MS SQL 2005 中创建触发器
我需要更新记录状态(字段名称 IsActivate - 这保存记录的状态),但需要首先检查数据是否在另一个表中使用(意味着表与另一个表有关系)。如果我想要的数据to update 用于另一个表中,不允许更新,否则应该更新。如何使用 MS SQL 2005 创建触发器来实现此方法?或者可以使用 SP 来实现吗?如何?
I need to update the record status(field name IsActivate-this holds the status of record) but needs to check first if the data is used in another table(meaning table is having a relationship with another table).If the data that I want to update is used in another table, update is not allowed else it should be updated.How can I can create trigger to achieved this approach using MS SQL 2005?Or this can be achieved using SP?How?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
如果我是你,我会避免使用触发器。您可以在 SP 中轻松完成此操作。
用于更新记录的 SP 应该执行如下操作:-
@value
@value< /code> 在
SELECT
语句中,如SELECT @result = count(1) FROM tableToCheck WHERE columnToCheck = @value
@result
= 0,继续并触发现有的UPDATE
语句@resul
t <> 0,在您的业务场景中做任何您需要做的事情,比如更新一个输出参数来指示您的更新失败如果您想了解上述逻辑的语法,您可以查找 MSDN(如果您使用的是 SQL Server 或 MySQL 文档(如果使用 MySQL 或 Oracle 帮助(如果您使用的是 Oracle)。如果有任何其他引擎,只需谷歌“
CREATE PROCEDURE
”然后,如果您对语法有任何具体问题,请随时将其与您的代码一起发布到此处,我们将很乐意提供帮助你和他们在一起!!
I would avoid using a trigger for this if i were you. You can easily do this in an SP.
Your SP for updating the record should do something like this:-
@value
@value
in aSELECT
statment in something likeSELECT @result = count(1) FROM tableToCheck WHERE columnToCheck = @value
@result
= 0, go ahead and fire your existingUPDATE
statement@resul
t <> 0, do whatever you need to do in your business scenario like maybe update an Output param to indicate that your update failedIn case you want to know the syntax of the above logic, you can look up MSDN if you are using SQL Server or the MySQL docs if using MySQL or Oracle help if you are using Oracle. If any other engine, just google "
CREATE PROCEDURE <engineName>
"And then if you have any specific questions with the syntax, feel free to post them here with your code and we will be glad to help you with them!!