Solved

question about trigger

Posted on 2012-03-16
8
236 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 40

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 53

Expert Comment

by:Huseyin KAHRAMAN
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 40

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 53

Expert Comment

by:Huseyin KAHRAMAN
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 40

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 53

Expert Comment

by:Huseyin KAHRAMAN
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
connection to SQL 2012 error in windows 10 18 49
Search Text in Views 2 28
SQL query joining 6 tables in asp.net 4 31
learning MS SSIS 13 23
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

739 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