Solved

question about trigger

Posted on 2012-03-16
8
230 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
Comment Utility
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
Comment Utility
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
Comment Utility
Where in this code can i add the cursor?

Thanks


bibi
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks regards bibi
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

771 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

12 Experts available now in Live!

Get 1:1 Help Now