开发者

After Insert Trigger....Looping

开发者 https://www.devze.com 2023-04-01 11:07 出处:网络
What would the processing load concern be if I had an \"After Insert\" trigger created on a table and in that trigger I performed a While loop to iterate through \"potentially\" multiple rows?

What would the processing load concern be if I had an "After Insert" trigger created on a table and in that trigger I performed a While loop to iterate through "potentially" multiple rows?

End result is I will 99.999% of the time have only 1 row, but as the future is unpredictable i also want to be able to handle multiple rows being inserted.

Trigger Model: 1) Insert information into the table 2) Create views specific to the client, via stored procedures (if possible)

What Say You? :)

Haven't fully develop开发者_StackOverflow社区ed but this is the design i am looking for, may not be structurally sound but should get the point acrossed.

CREATE TRIGGER dbo.New_Client_Setup 
   ON  dbo.client 
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    --Fill Temp Table
     select * into #clients
     from inserted

     --Iterate through Temp Table
     While (select count(*) from #clients) <> 0 BEGIN
         declare @id int, @clnt nvarchar(10)

         select top(1)
            @id = id
            , @clnt = short
         order by id desc

         Execute dbo.sp_Create_View_Client ( @id, @clnt )

         -- Drop used ID
         delete from #clients
         where id = @id
     END

     Drop table #clients
END
GO

Again, observe the design of the trigger not necessarily the syntactic sugar


Design wise, reading the comments, I think you do not neccesarily need to do this in triggers. I would say you should do it as part of your insert statement in transactions - i.e. do the insert, and then do the loop that you want to do (whatever that does - execute dbo.sp_Create_View_Client)...

The second thing I would mention is what exactly is dbo.sp_Create_View_Client doing - is it a must-dependent on the insert? Meaning, what happens if the insert works fine, and the trigger fails? I would maybe do the whole insert and execute of the SP all in one transaction, so as to preserve data integrity.

0

精彩评论

暂无评论...
验证码 换一张
取 消