在使用触发器插入/更新之前,检查某些列中的特定值 [英] Checking some columns for specific values before insert/update using a trigger
问题描述
我有一个包含几列的数据库FOO,其中我有一列"Url".我需要在插入/更新之前编写一个触发器,以检查Url列是否较新的值匹配任何现有值,即"hello"(除某些预定义值之外).这意味着,如果多次插入或更新"hello",将不会发生错误,否则将检查是否重复.如果发现某些异常,则中止插入更新.这还将返回一些代码,以便我的调用插入/更新的脚本将知道发生了故障.我知道可能还有其他解决方法,但我将需要采用这种方式.我对SQL很陌生.
I have a database FOO with several columns, among those I have one column "Url". I need to write a trigger before insert/update that will check the Url columns whether the newer value matches any existing values, i.e. "hello" except some predefined value. That means if "hello" is inserted or updated multiple times no error will happen otherwise it will check for duplicity. And if it finds some aborts the insertion update. This will also return some code so that my script calling for the insertion/update will know a failure has occurred. I know there might be other workarounds but I will need to have it this way. I am pretty new to SQL.
Foo {
Url
}
这是算法
Before update insert
if new value of Url is not "hello1" o "hello 2"
check if new value of Url already exists in Foo.Url if so abort otherwise allow update/insert
return something if aborted/success
推荐答案
尝试类似的方法..您需要索引表.
try something like this.. you'll need to index your table..
IF EXISTS(SELECT URL FROM Foo.Url)
BEGIN
SELECT 'URL Exists Already'
END
ELSE
BEGIN
INSERT/UPDATE
END
这篇关于在使用触发器插入/更新之前,检查某些列中的特定值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!