转来一条语句用于更新学生表中学生的年龄:
UPDATE [students] SET [age]=@age WHERE studentID=[@id]想用一个触发器,在Update之前先检查students表中存是存在studentID=@id的学生,
如果不存在空上条语就自动变成插入
INSERT INTO students (studnetID,age) VALUES (@studentID,@id)
如果检测存就直接更新[SQL Server 2005/8]
UPDATE [students] SET [age]=@age WHERE studentID=[@id]想用一个触发器,在Update之前先检查students表中存是存在studentID=@id的学生,
如果不存在空上条语就自动变成插入
INSERT INTO students (studnetID,age) VALUES (@studentID,@id)
如果检测存就直接更新[SQL Server 2005/8]
if @@rowcount=0
INSERT INTO students (studnetID,age) VALUES (@studentID,@id)
UPDATE [students] SET [age]=@age WHERE studentID=[@id]
IF @@ROWCOUNT=0
INSERT INTO students (studnetID,age) VALUES (@studentID,@id)
COMMIT TRAN
最好是触发器,不用触发器也行,不过外层只是专一个UPDATE 的 SQL 语句过来。不想再写多的语句。 SQL SERVER 根据外在传来的T-SQL自动的判断是insert还是去update
on students
INSTEAD OF UPDATE
asUPDATE [students] SET [age]=i.age
from inserted i
WHERE i.studentID=[students].studentIDINSERT INTO students (studnetID,age)
select studnetID,age
from inserted i
where not exists (
select 1 from students
WHERE i.studentID=[students].studentID
)