Solved

question about trigger

Posted on 2012-03-16
8
231 Views
Last Modified: 2012-03-21
Hello,

Is that the command Insert Into Select From that insert multiple rows does not trigger the trigger line by line

Create Trigger  
 [test].[table_INS] 
 On [test].[table] 
 After INSERT 
 As 
Begin 
  Declare @blank   NVARCHAR(1) 
  Declare @datsys  DATETIME 
  Declare @heusys  NVARCHAR(10) 
  Declare @hh      NVARCHAR(2) 
  Declare @mm      NVARCHAR(2) 
  Declare @ss      NVARCHAR(2) 
  Declare @scuser  NVARCHAR(5) 
  Declare @osuser  NVARCHAR(20) 
  Declare @adrcli  NVARCHAR(30) 
  Declare @scseq   INT 
  Declare @scevt   NVARCHAR(10) 
  Declare @cle1    NVARCHAR(50) 
  Declare @cle2    NVARCHAR(50) 
  Declare @ocle1   NVARCHAR(50) 
  Declare @ocle2   NVARCHAR(50) 
  set nocount on 
  set @blank = ' ' 
  set @datsys = getdate() 
  set @hh = datename(hour,@datsys) 
  set @mm = datename(minute,@datsys) 
  set @ss = datename(second,@datsys) 
  If len(@hh)=1 set @hh = '0'+@hh 
  If len(@mm)=1 set @mm = '0'+@mm 
  If len(@ss)=1 set @ss = '0'+@ss 
  set @heusys = @hh+@mm+@ss 
  Select TOP 1 
    @scuser = ADOUSR_0 
  , @osuser = SYSUSR_0 
  , @adrcli = ADRCLI_0 
  From test.ALOGIN 
  Where BDDID_0=CONVERT(nvarchar(10),@@spid) and FLG_0=2 
  If @scuser is null 
  Begin 
   set @scuser = @blank 
   set @osuser = 'External' 
   set @adrcli = 'Unknown' 
  End 
  set @scevt = 'INSERT' 
  Select 
     @cle1 = Inserted.dos_0 
   , @cle2 = Inserted.TYP_0 + '~' + Inserted.NUM_0 
  From Inserted 
  Insert into test.AUDITH 
   (SEQ_0,EVT_0,TBL_0,DAT_0,HOU_0,ADOUSR_0,SYSUSR_0 
   ,ADRCLI_0,ID1_0,ID2_0,STA_0,STABI_0) 
   values (-@@spid,@scevt,'table',@datsys,@heusys,@scuser,@osuser 
  , @adrcli,@cle1,@cle2,1,2) 
  set @scseq = @@identity 
  Update test.AUDITH set SEQ_0 = @scseq where ROWID=@scseq 
  set nocount off 
End 

Open in new window


Thanks

regards

Bibi
0
Comment
Question by:bibi92
  • 3
  • 3
  • 2
8 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 37730363
That is correct - this would be a batched operation and your trigger must be capabale to handle it if needed.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 37730424
you can create a cursor in the trigger

cursor select * from inserted;
open cursor
loop cursor
  for each record do...
close cursor
0
 

Author Comment

by:bibi92
ID: 37730558
Where in this code can i add the cursor?

Thanks


bibi
0
 
LVL 39

Expert Comment

by:lcohan
ID: 37730584
I recommend do not use CURSOR in triggers as they can induce performance issues but pseudo cursor like


WHILE (select count(*) from INSERTED) > 1

--or DELETED depending on trigger action
--do your stuff here

LOOP
END
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 51

Expert Comment

by:HainKurt
ID: 37730610
something like this

' existing code here Line 1-44

DECLARE @cle1 varchar(100), @cle1 varchar(100)
DECLARE c_InsertedRows Cursor FOR Select Inserted.dos_0, Inserted.TYP_0 + '~' + Inserted.NUM_0 From Inserted; 
Open c_InsertedRows;
Fetch NEXT FROM c_InsertedRows INTO @cle1, @cle2;
While (@@FETCH_STATUS <> -1)
BEGIN
  ' your code goes here Line 45-55, except line 45-48, which you comment out/delete
  Fetch NEXT FROM c_InsertedRows INTO @cle1, @cle2; 
END
CLOSE c_InsertedRows;
DEALLOCATE c_InsertedRows;

' existing code line 56-57

Open in new window

0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 37730613
It would be something like code below but PLEASE test it first:

Create Trigger  
 [test].[table_INS]
 On [test].[table]
 After INSERT
 As
Begin
  Declare @blank   NVARCHAR(1)
  Declare @datsys  DATETIME
  Declare @heusys  NVARCHAR(10)
  Declare @hh      NVARCHAR(2)
  Declare @mm      NVARCHAR(2)
  Declare @ss      NVARCHAR(2)
  Declare @scuser  NVARCHAR(5)
  Declare @osuser  NVARCHAR(20)
  Declare @adrcli  NVARCHAR(30)
  Declare @scseq   INT
  Declare @scevt   NVARCHAR(10)
  Declare @cle1    NVARCHAR(50)
  Declare @cle2    NVARCHAR(50)
  Declare @ocle1   NVARCHAR(50)
  Declare @ocle2   NVARCHAR(50)
  set nocount on
  set @blank = ' '
  set @datsys = getdate()
  set @hh = datename(hour,@datsys)
  set @mm = datename(minute,@datsys)
  set @ss = datename(second,@datsys)
  If len(@hh)=1 set @hh = '0'+@hh
  If len(@mm)=1 set @mm = '0'+@mm
  If len(@ss)=1 set @ss = '0'+@ss
  set @heusys = @hh+@mm+@ss

if (select count(*) from inserted) = 1
begin -- single insert

  Select TOP 1
    @scuser = ADOUSR_0
  , @osuser = SYSUSR_0
  , @adrcli = ADRCLI_0
  From test.ALOGIN
  Where BDDID_0=CONVERT(nvarchar(10),@@spid) and FLG_0=2
  If @scuser is null
  Begin
   set @scuser = @blank
   set @osuser = 'External'
   set @adrcli = 'Unknown'
  End
  set @scevt = 'INSERT'
  Select
     @cle1 = Inserted.dos_0
   , @cle2 = Inserted.TYP_0 + '~' + Inserted.NUM_0
  From Inserted

  Insert into test.AUDITH
   (SEQ_0,EVT_0,TBL_0,DAT_0,HOU_0,ADOUSR_0,SYSUSR_0
   ,ADRCLI_0,ID1_0,ID2_0,STA_0,STABI_0)
   values (-@@spid,@scevt,'table',@datsys,@heusys,@scuser,@osuser
  , @adrcli,@cle1,@cle2,1,2)
  set @scseq = @@identity

  Update test.AUDITH set SEQ_0 = @scseq where ROWID=@scseq

  set nocount off

end -- single insert

if (select count(*) from inserted) > 1
declare @loop_table table (id int)
insert into @loop_table select id from inserted

while (select count(*) from @loop_table) > 0
begin -- loop
      set @scseq = (select top 1 id from @loop_table order by id)

      Select TOP 1
    @scuser = ADOUSR_0
  , @osuser = SYSUSR_0
  , @adrcli = ADRCLI_0
  From test.ALOGIN
  Where BDDID_0=CONVERT(nvarchar(10),@@spid) and FLG_0=2
  If @scuser is null
  Begin
   set @scuser = @blank
   set @osuser = 'External'
   set @adrcli = 'Unknown'
  End
  set @scevt = 'INSERT'
  Select
     @cle1 = Inserted.dos_0
   , @cle2 = Inserted.TYP_0 + '~' + Inserted.NUM_0
  From Inserted

  Insert into test.AUDITH
   (SEQ_0,EVT_0,TBL_0,DAT_0,HOU_0,ADOUSR_0,SYSUSR_0
   ,ADRCLI_0,ID1_0,ID2_0,STA_0,STABI_0)
   values (-@@spid,@scevt,'table',@datsys,@heusys,@scuser,@osuser
  , @adrcli,@cle1,@cle2,1,2)
  set @scseq = @@identity

  Update test.AUDITH set SEQ_0 = @scseq where ROWID=@scseq

  delete from @loop_table where id = @scseq -- this is to removed processed row and skip to next
      
end -- loop

End
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 37730636
or use this structure

' existing code here Line 1-44

DECLARE @cle1 varchar(100), @cle2 varchar(100);
DECLARE c_InsertedRows Cursor FOR Select Inserted.dos_0, Inserted.TYP_0 + '~' + Inserted.NUM_0 From Inserted; 

Open c_InsertedRows;

Fetch NEXT FROM c_InsertedRows INTO @cle1, @cle2;

WHILE @@FETCH_STATUS = 0
BEGIN
  ' your code goes here Line 48-55
  Fetch NEXT FROM c_InsertedRows INTO @cle1, @cle2
END;

CLOSE c_InsertedRows;
DEALLOCATE c_InsertedRows;

' existing code line 56-57

Open in new window


http://msdn.microsoft.com/en-us/library/ms187308.aspx
0
 

Author Closing Comment

by:bibi92
ID: 37747019
Thanks regards bibi
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now