deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action. For example, to retrieve all values in the deleted table, use:SELECT * FROM deleted
Create TRIGGER [dbo].[test1_insupd]
ON [dbo].[test1]
FOR INSERT, UPDATE
AS
BEGIN
declare @f1 int
declare @f2 varchar(10)
declare @vishg char(1)
select @f1 = f1, @f2 = f2, @vishg = vishg from inserted
if UPDATE ( visHG ) and @vishg = '1'
begin
IF EXISTS(select * from test2 where f1 = @f1)
update test2 set f2 = 'true in t1' where f1 = @f1
else
insert into test2(f2) values( 'true in t1')
end
else
begin
IF EXISTS(select * from test2 where f1 = @f1)
update test2 set f2 = 'not true' where f1 = @f1
else
insert into test2(f2) values('not true')
end
END
ON [dbo].[test1]
FOR INSERT, UPDATE
AS
BEGIN
declare @f1 int
declare @f2 varchar(10)
declare @vishg char(1)
select @f1 = f1, @f2 = f2, @vishg = vishg from inserted
if UPDATE ( visHG ) and @vishg = '1'
begin
IF EXISTS(select * from test2 where f1 = @f1)
update test2 set f2 = 'true in t1' where f1 = @f1
else
insert into test2(f2) values( 'true in t1')
end
else
begin
IF EXISTS(select * from test2 where f1 = @f1)
update test2 set f2 = 'not true' where f1 = @f1
else
insert into test2(f2) values('not true')
end
END