A MS SQL insert trigger causes some grief as it returns a result. Is there a way to disable any result from being sent? I've used 'set nocount on', but that doesn't seem to do much...
create trigger tr_insert_raw_data
on raw_data
instead of insert
as
begin
set nocount on
declare @query varchar(max);
declare @rev int;
declare @id int;
declare @oldrev int;
declare @contextinfo VARBINARY(128)
select @contextinfo = context_info()
if @contextinfo = 0x00001
begin
insert into raw_data select * from inserted
return
end
select * into #query from inserted
set @id = (select count(distinct id) from raw_data);
set @id += 1;
insert into revisions (username, hostname, ts) values (SYSTEM_USER, HOST_NAME(), GETDATE())
set @rev = (select max(id) from revisions);
select @oldrev = revision_id from inserted;
if @oldrev is null set @oldrev=@rev-1;
select * into #inserted from inserted
update #inserted set revision_id = @rev, id = @id
select * from #inserted
insert into raw_data select * from #inserted
insert into edges (a, b) values (@oldrev, @rev)
end
what kind of a result are you getting? After a quick review of your query my first thought is that you're getting results due to the
select * from #inserted
line you have (third line from bottom). This line tells me that you are requesting results which is probably why you're getting some?
If you do need values selected from a table to modify or custom select out of, perhaps CTE's will help you out? (Common Table Expressions)