How to prevent or allow a insert trigger ?
-
I have a issue when I create a trigger for insert or update Create trigger In_Name on CheckStatus for insert, update as declare @id int Select @id=id from inserted where id=@id if exists (Select * from CheckStatus where id=@id) begin rollback transaction update CheckStatus Set levels=levels+1 where id=@id end else insert into CheckStatus (id,levels) values (@id,1) But the trigger do not execute my opinion (I want to update "levels" when id field exists, inversely inserting id and levels) Please, giving a hand, thanks a lot
-
I have a issue when I create a trigger for insert or update Create trigger In_Name on CheckStatus for insert, update as declare @id int Select @id=id from inserted where id=@id if exists (Select * from CheckStatus where id=@id) begin rollback transaction update CheckStatus Set levels=levels+1 where id=@id end else insert into CheckStatus (id,levels) values (@id,1) But the trigger do not execute my opinion (I want to update "levels" when id field exists, inversely inserting id and levels) Please, giving a hand, thanks a lot
If your database design needs you to update primary keys (ID Fields) you have bigger problems that trigger design. A primary field should never be modified, with the possible exception of deploying data across different systems.
Never underestimate the power of human stupidity RAH
-
If your database design needs you to update primary keys (ID Fields) you have bigger problems that trigger design. A primary field should never be modified, with the possible exception of deploying data across different systems.
Never underestimate the power of human stupidity RAH
-
I have a issue when I create a trigger for insert or update Create trigger In_Name on CheckStatus for insert, update as declare @id int Select @id=id from inserted where id=@id if exists (Select * from CheckStatus where id=@id) begin rollback transaction update CheckStatus Set levels=levels+1 where id=@id end else insert into CheckStatus (id,levels) values (@id,1) But the trigger do not execute my opinion (I want to update "levels" when id field exists, inversely inserting id and levels) Please, giving a hand, thanks a lot
Select @id=id from inserted where id=@id
There's your issue - @id is going to be null as you have only just declared it in the line above. The trigger does fire it just does not do anything as @id is always going to be null. Get rid of the
where id=@id
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Select @id=id from inserted where id=@id
There's your issue - @id is going to be null as you have only just declared it in the line above. The trigger does fire it just does not do anything as @id is always going to be null. Get rid of the
where id=@id
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
ok, I show all code again after editing CREATE trigger In_LoaiDG1 on KhaoSat_LoaiDG1 for insert,update as declare @NgayDG smalldatetime declare @MaLoaiTT int Select @MaLoaiTT=MaLoaiTT,@NgayDG=NgayDG from inserted if exists (Select count(*) from KhaoSat_LoaiDG1 where KhaoSat_LoaiDG1.MaLoaiTT=@MaLoaiTT and KhaoSat_LoaiDG1.NgayDG=@NgayDG) begin rollback transaction update KhaoSat_LoaiDG1 set Muc1=Muc1+1 where MaLoaiTT=@MaLoaiTT and NgayDG=@NgayDG end else insert into KhaoSat_LoaiDG1(MaLoaiTT,Muc1,NgayDG) values (@MaLoaiTT,1,@NgayDG) Update is ok but not insert.I do not know why, please help me.
-
ok, I show all code again after editing CREATE trigger In_LoaiDG1 on KhaoSat_LoaiDG1 for insert,update as declare @NgayDG smalldatetime declare @MaLoaiTT int Select @MaLoaiTT=MaLoaiTT,@NgayDG=NgayDG from inserted if exists (Select count(*) from KhaoSat_LoaiDG1 where KhaoSat_LoaiDG1.MaLoaiTT=@MaLoaiTT and KhaoSat_LoaiDG1.NgayDG=@NgayDG) begin rollback transaction update KhaoSat_LoaiDG1 set Muc1=Muc1+1 where MaLoaiTT=@MaLoaiTT and NgayDG=@NgayDG end else insert into KhaoSat_LoaiDG1(MaLoaiTT,Muc1,NgayDG) values (@MaLoaiTT,1,@NgayDG) Update is ok but not insert.I do not know why, please help me.
It looks like you are performing an insert within your trigger on the table the trigger is running from
CREATE trigger In_LoaiDG1 on KhaoSat_LoaiDG1
insert into KhaoSat_LoaiDG1
Any clues as to why this may be a bad idea? Hint - infinity... Just remove:
else
insert into KhaoSat_LoaiDG1(MaLoaiTT,Muc1,NgayDG) values (@MaLoaiTT,1,@NgayDG)and see what happens.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens