Simple
SQLServer触发器内对更新的数据进行判断，符合条件的操作则回滚。rollback关键字为回滚事务，raiseroor为自助生成SQL异常信息。代码如下：
CREATE TRIGGER [dbo].[tri_after_modify] ON [dbo].[tablename] AFTER INSERT, UPDATE, DELETE AS if(select COUNT(*) from deleted) > 1 begin rollback raiserror ('错误信息：一次只能修改一个。',17,1) return end
一句insert批量插入，并需要在触发器里写各种判断逻辑，这时候需要用游标，代码如下：
除了把游标主键字段（下面红色id）改一下和循环中的业务逻辑按照自己实际情况写，其他的黑色字体部分无需改动。然后在循环中使用@subid代表其中一行的主键了。
declare @subid int --设定游标循环，更新配送关系表，以配送关系id为单位 declare subdata_curr cursor for select id from inserted --这里查出主键，以主键为游标标识 open subdata_curr --打开游标 fetch next from subdata_curr into @subid --开始循环游标变量 while(@@fetch_status=0) begin --业务逻辑 declare @in_count int, select @in_count = i.入库数量 from inserted i where i.id = @subid fetch next from subdata_curr into @subid --循环游标变量 end close subdata_curr deallocate subdata_curr --关闭、释放游标
不知各位项目中会不会保存个人信息的星座属性，我这是项目中遇到了，把代码贴出来供需要的人吧，
SQL Server 函数创建语句：
CREATE FUNCTION [dbo].[f_星座]
(
@birthdate datetime
)
returns nvarchar(6)
AS
begin
declare @month int, @day int, @result nvarchar(6)
set @month = DATEPART(MONTH, @birthdate)
set @day = DATEPART(DAY, @birthdate)
if ((@month = 1 and @day >= 20) or (@month = 2 and @day <= 18))
begin
set @result = '水瓶座';
end
else if ((@month = 2 and @day >= 19) or (@month = 3 and @day <= 20))
begin
set @result = '双鱼座';
end
else if ((@month = 3 and @day >= 21) or (@month = 4 and @day <= 19))
begin
set @result = '白羊座';
end
else if ((@month = 4 and @day >= 20) or (@month = 5 and @day <= 20))
begin
set @result = '金牛座';
end
else if ((@month = 5 and @day >= 21) or (@month = 6 and @day <= 21))
begin
set @result = '双子座';
end
else if ((@month = 6 and @day >= 22) or (@month = 7 and @day <= 22))
begin
set @result = '巨蟹座';
end
else if ((@month = 7 and @day >= 23) or (@month = 8 and @day <= 22))
begin
set @result = '狮子座';
end
else if ((@month = 8 and @day >= 23) or (@month = 9 and @day <= 22))
begin
set @result = '处女座';
end
else if ((@month = 9 and @day >= 23) or (@month = 10 and @day <= 23))
begin
set @result = '天秤座';
end
else if ((@month = 10 and @day >= 24) or (@month = 11 and @day <= 22))
begin
set @result = '天蝎座';
end
else if ((@month = 11 and @day >= 23) or (@month = 12 and @day <= 21))
begin
set @result = '射手座';
end
else if ((@month = 12 and @day >= 22) or (@month = 1 and @day <= 19))
begin
set @result = '摩羯座';
end
return @result
end
这个不知道有没有项目会用。。反正我遇到了。。。写了一份留着吧。。
根据日期获取年龄（传入yyyy-mm-dd）：
//根据日期获取年龄（传入yyyy-mm-dd）
function getAgeByBornDate(bornDate) {
var myDate = new Date();
var month = myDate.getMonth() + 1;
var day = myDate.getDate();
var age = myDate.getFullYear() - parseInt(bornDate.substring(0, 4)) - 1;
if (parseInt(bornDate.substring(5, 7)) < month ||
parseInt(bornDate.substring(5, 7)) == month && parseInt(bornDate.substring(8, 10)) <= day) {
age++;
}
return age;
}
根据日期获取星座（传入yyyy-mm-dd）：
//根据日期获取星座（传入yyyy-mm-dd）
function getConstellationByBornDate(bornDate) {
var month = parseInt(bornDate.substring(5, 7));
var day = parseInt(bornDate.substring(8, 10));
if ((month == 1 && day >= 20) || (month == 2 && day <= 18)) {
return "水瓶座";
} else if ((month == 2 && day >= 19) || (month == 3 && day <= 20)) {
return "双鱼座";
} else if ((month == 3 && day >= 21) || (month == 4 && day <= 19)) {
return "白羊座";
} else if ((month == 4 && day >= 20) || (month == 5 && day <= 20)) {
return "金牛座";
} else if ((month == 5 && day >= 21) || (month == 6 && day <= 21)) {
return "双子座";
} else if ((month == 6 && day >= 22) || (month == 7 && day <= 22)) {
return "巨蟹座";
} else if ((month == 7 && day >= 23) || (month == 8 && day <= 22)) {
return "狮子座";
} else if ((month == 8 && day >= 23) || (month == 9 && day <= 22)) {
return "处女座";
} else if ((month == 9 && day >= 23) || (month == 10 && day <= 23)) {
return "天秤座";
} else if ((month == 10 && day >= 24) || (month == 11 && day <= 22)) {
return "天蝎座";
} else if ((month == 11 && day >= 23) || (month == 12 && day <= 21)) {
return "射手座";
} else if ((month == 12 && day >= 22) || (month == 1 && day <= 19)) {
return "摩羯座";
}
return;
}
身份证号码验证（理论15、18位都可用）：
//身份证验证
function identityCodeValid(code) {
if (code + "" == "") {
return;
}
var city = { 11: "北京", 12: "天津", 13: "河北", 14: "山西", 15: "内蒙古", 21: "辽宁", 22: "吉林", 23: "黑龙江 ", 31: "上海", 32: "江苏", 33: "浙江", 34: "安徽", 35: "福建", 36: "江西", 37: "山东", 41: "河南", 42: "湖北 ", 43: "湖南", 44: "广东", 45: "广西", 46: "海南", 50: "重庆", 51: "四川", 52: "贵州", 53: "云南", 54: "西藏 ", 61: "陕西", 62: "甘肃", 63: "青海", 64: "宁夏", 65: "新疆", 71: "台湾", 81: "香港", 82: "澳门", 91: "国外 " };
var tip = "";
var pass = true;
//验证身份证格式（6个地区编码，8位出生日期，3位顺序号，1位校验位）
if (!code || !/^\d{6}(18|19|20)?\d{2}(0[1-9]|1[12])(0[1-9]|[12]\d|3[01])\d{3}(\d|X)$/i.test(code)) {
tip = "身份证号格式错误";
pass = false;
}
else if (!city[code.substr(0, 2)]) {
tip = "身份证号地址编码错误";
pass = false;
}
else {
//18位身份证需要验证最后一位校验位
if (code.length == 18) {
code = code.split('');
//∑(ai×Wi)(mod 11)
//加权因子
var factor = [7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2];
//校验位
var parity = [1, 0, 'X', 9, 8, 7, 6, 5, 4, 3, 2];
var sum = 0;
var ai = 0;
var wi = 0;
for (var i = 0; i < 17; i++) {
ai = code[i];
wi = factor[i];
sum += ai * wi;
}
var last = parity[sum % 11];
if (parity[sum % 11] != code[17]) {
tip = "身份证号校验位错误";
pass = false;
}
}
}
if (!pass) alert(tip);
return pass;
}
身份证获取生日(获取到的为yyyy-mm-dd格式的字符串)：
//身份证获取生日(yyyy-mm-dd)
function getBirthdayFromIdCard(idCard) {
var birthday = "";
if (idCard != null && idCard != "") {
if (idCard.length == 15) {
birthday = "19" + idCard.substr(6, 6);
} else if (idCard.length == 18) {
birthday = idCard.substr(6, 8);
}
birthday = birthday.replace(/(.{4})(.{2})/, "$1-$2-");
}
return birthday;
}
身份证获取性别：
//身份证获取性别
function getSexByID(id) {
var sex;
if (id.length == 15) {
if (parseInt(id.charAt(14) / 2) * 2 != id.charAt(14)) {
sex = "男";
}
else {
sex = "女";
}
}
else if (id.length == 18) {
if (parseInt(id.charAt(16) / 2) * 2 != id.charAt(16)) {
sex = "男";
}
else {
sex = "女";
}
}
return sex;
};