bibi92
asked on
question about trigger
Hello,
Is that the command Insert Into Select From that insert multiple rows does not trigger the trigger line by line
Thanks
regards
Bibi
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
Thanks
regards
Bibi
That is correct - this would be a batched operation and your trigger must be capabale to handle it if needed.
you can create a cursor in the trigger
cursor select * from inserted;
open cursor
loop cursor
for each record do...
close cursor
cursor select * from inserted;
open cursor
loop cursor
for each record do...
close cursor
ASKER
Where in this code can i add the cursor?
Thanks
bibi
Thanks
bibi
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
WHILE (select count(*) from INSERTED) > 1
--or DELETED depending on trigger action
--do your stuff here
LOOP
END
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
or use this structure
http://msdn.microsoft.com/en-us/library/ms187308.aspx
' 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
http://msdn.microsoft.com/en-us/library/ms187308.aspx
ASKER
Thanks regards bibi