Solved

question about trigger

Posted on 2012-03-16
8
233 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
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 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
 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

830 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