Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

question about trigger

Posted on 2012-03-16
8
Medium Priority
?
239 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 59

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 59

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 40

Accepted Solution

by:
lcohan earned 2000 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 59

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 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