insert trigger question
-
Hi, I'm confused about the 'inserted' virtual table.
Here is my trigger:
************************
CREATE TRIGGER [update_totalConns] ON [netro].[statTotals] FOR INSERT
AS
declare @sname varchar(50)
declare @pname varchar(50)
declare @h datetime
declare @nc int
select @sname = (select wmsname from inserted)
select @pname = (select ppname from inserted)
select @h = (select hours from inserted)
select @nc = (select numConnections from inserted)
exec update_totalConnections @wmsname=@sname,@ppname=@pname,@hours=@h,@newconnects=@nc
**********************************************
These lines: select @sname = (select wmsname from inserted)
are returning more then one value.
I thought that when you refered to the inserted table it returned the values from the inserted ro that fires the trigger (apparently not
how should I be refering to the inserted values?
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
In sql server triggers fire for a statement. Meaning that if you insert 10 rows in a single sattement the inserted table contains 10 rows. I am not sure what you are doing within the sp you are calling so I can't give you much more advice. If you need to handle single rows in a trigger you wuld need to use a cursor which is not a recommended practice in SQL Server. By the way you could get all the variables using a single select;
Select @sname = wmsname, @pname = ppname, @h= hours, @nc = numConnections From inserted.
If you post what you are doing in the sp I may be able to suggest an alternate method that handles multi-row inserts.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Thankyou, Your first comment solved the main cofusion for me. Knowing that the trigger fires for the statement rather then for each row makes a difference. Although it's not reccomended I have a felling I'll end up with a cursor in my trigger.
The business requirement concerns windows media server logs. The logs contain a field called 'totalclients' unfortunetly its for the server not for the publishing point. Anyway, the logs are rolled up to one hour chunks in sql and what I have to work with is 'new connections' in a given hour and the average duration of those connections.
My tables contain unique entries for servername, publishingpoint name, and hour 'yyyy-mm-dd hh:00:00'
So the procedure below says, given a srvname,ppname, hour, and the number of new connections, look back and see how many people from the previous hours would still be connected based on thier avgDuration. Sum these, add the number of new connections and make a row in another table. sname,pname,hour,totalConnections. It is not 1005 accurate and has a smoothing effect on the data but this is acceptable.
The sp works as expected when called directly, or when one row is inserted. Your information will help me refine it to work for the bulk inserts. (I am more then a little nervous about the performance issues of this system but for business reasons I need to have a solution today, and than at my leisure I can work up a more efficient method. probably a series of nested derived tables or something.)
here is my proc.
CREATE PROCEDURE [dbo].[update_totalConnections]
@wmsname varchar(50),
@ppname varchar(50),
@hours datetime,
@newconnects int
AS
/*
given a new row in statTotals
*/
declare @newhour datetime
declare @accumulator int
declare @tmpCons int
set @accumulator=0
--set these from 'inserted' triggers
--
SET CONCAT_NULL_YIELDS_NULL OFF
declare @counter int
set @counter = 0
while @counter < 24
begin
set @counter = @counter + 1
set @newhour = dateadd(hh,@counter*-1,@hours)
select @tmpCons = (
SELECT numConnections
FROM netro.statTotals
where avgDuration > 360*@counter and wmsName=@wmsname and ppname=@ppname and hours=@newhour
 
set @accumulator = @accumulator + isnull(@tmpCons,0)
end
--so now that I have added up all connections that were still on-line at this hour, write it to the totalConnections table.
insert into netro.statTotalConnections (wmsname,ppname,hours,totalConnections) values
(@wmsName,@ppname,@hours,@accumulator+@newconnects)
GO
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
yup the performance issues made it a no go on running the cursor in the trigger to call this proc..loop within a loop, was causing the remote bulk insert to fail.
I'll have to find another around or through this issue. I'm beginning to think I should launch a forum site dedicated to decyphering different log formats to get what ya need outta them.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply