Wednesday 18 January 2012

EXAMPLE OF INSTEAD OF TRIGGER

I have one table and view that is define below:
CREATE TABLE [B]
(
[ID] [int] ,
[NAME] [varchar](50) ,
[SAL] [decimal](18, 2) ,
[TA] [decimal](18, 2)
)
create view [v] as select id,name,sal from B

TRIGGER:

create trigger tr0 on v instead of insert,delete
as
begin
insert into b select id,name,sal,(sal*.1) from inserted
delete from b where id=(select id from deleted)
end

TRIGGER:
alter trigger tr00 on v instead of update
as
begin
if update(name) or update(sal)
update b set name=(select name from inserted),sal=(select sal from inserted),ta=((select sal from inserted)*.1) where id=(select id from deleted)
end

No comments:

Post a Comment