May 17, 2012 at 3:50 am
Hi all.
I'm working with sql 2005, but my trigger some time not work. help me please!
My trigger:
Create Trigger ChungTuKho_ArchiveLogTrg On ChungTuKho
With Encryption
After Insert,Update,Delete As
Declare @v_DuLieu XML;
If Exists (Select null From Inserted ChungTuKho Where isNull(ChungTuKho.TenForm,'')<>'DieuChuyenKho')
Begin
Select @v_DuLieu = (Select MaPK,MaLoaiChiPhiKho,KyHieuHoaDon,SoHoaDon,NgayHoaDon,SoChungTu,NgayHachToan
From Inserted As ChungTuKho
Where isNull(ChungTuKho.TenForm,'')<>'DieuChuyenKho' FOR XML AUTO, ELEMENTS);
Insert TruyenNhan.dbo.ArchiveLog (MaTruyenNhan,DBname,TenTable,DuLieu)
Select A.Ma,DB_NAME(),'ChungTuKho',@v_DuLieu
From TruyenNhan.dbo.TruyenNhan A,TruyenNhan.dbo.DanhSachTable B
Where A.DatabaseTruyen = DB_NAME() And B.DBName = DB_NAME()
And A.InstanceTruyen = 1 And A.Ma = B.MaTruyenNhan And B.TenTable = 'ChungTuKho'
And @v_DuLieu is not null;
Insert TruyenNhan.dbo.DongBoLog (MaTruyenNhan,DBName,TenTable,MaPKExp)
Select B.Ma,'TasecoTH','ChungTuKho',ChungTuKho.MaPK
From Inserted ChungTuKho,TruyenNhan.dbo.TruyenNhan B,TruyenNhan.dbo.DanhSachTable C
Where B.DatabaseTruyen = 'TasecoTH' And B.InstanceTruyen = 1 And B.Ma = C.MaTruyenNhan
And C.DBName = DB_NAME() And isNull(ChungTuKho.TenForm,'')<>'DieuChuyenKho'
And C.TenTable = 'ChungTuKho'
And Not Exists (Select Null From TruyenNhan.dbo.DongBoLog
Where TenTable = 'ChungTuKho' And MaPKExp = Cast(ChungTuKho.MaPK as varchar)
And MaTruyenNhan = C.MaTruyenNhan And NhanVe = 0 And DBName = DB_NAME());
End;
Else
If Not Exists (Select Null From Inserted)
Begin
Update TruyenNhan.dbo.DongBoLog Set
Xoa = 1,
SoThuTuXoa = isNull(D.SoThuTuXoa,1)
From Deleted ChungTuKho,
TruyenNhan.dbo.TruyenNhan B,
TruyenNhan.dbo.DanhSachTable C Left join
(Select isNull(Max(SoThuTuXoa),0) + 1 SoThuTuXoa,MaTruyenNhan From TruyenNhan.dbo.DongBoLog Where DBName = DB_NAME() And Xoa = 1 Group by MaTruyenNhan) D On D.MaTruyenNhan = C.MaTruyenNhan
Where TruyenNhan.dbo.DongBoLog.TenTable = 'ChungTuKho'
And TruyenNhan.dbo.DongBoLog.MaPKExp = Cast(ChungTuKho.MaPK as varchar)
And B.DatabaseTruyen = DB_NAME()
And B.InstanceTruyen = 1 And C.MaTruyenNhan = B.Ma
And C.TenTable = 'ChungTuKho' And TruyenNhan.dbo.DongBoLog.NhanVe = 0
And C.DBName = DB_NAME()
And TruyenNhan.dbo.DongBoLog.MaTruyenNhan = B.Ma;
Insert TruyenNhan.dbo.DongBoLog (MaTruyenNhan,DBName,TenTable,MaPKExp,Xoa,SoThuTuXoa)
Select B.Ma,'TasecoTH','ChungTuKho',ChungTuKho.MaPK,1,isNull(D.SoThuTuXoa,1)
From Deleted ChungTuKho,
TruyenNhan.dbo.TruyenNhan B,
TruyenNhan.dbo.DanhSachTable C Left join (Select isNull(Max(SoThuTuXoa),0) + 1 SoThuTuXoa,MaTruyenNhan From TruyenNhan.dbo.DongBoLog Where DBName = DB_NAME() And Xoa = 1 Group by MaTruyenNhan) D On D.MaTruyenNhan = C.MaTruyenNhan
Where B.DatabaseTruyen = 'TasecoTH'
And B.InstanceTruyen = 1
And B.Ma = C.MaTruyenNhan
And C.DBName = DB_NAME()
And D.MaTruyenNhan = C.MaTruyenNhan
And isNull(ChungTuKho.TenForm,'')<>'DieuChuyenKho'
And C.TenTable = 'ChungTuKho'
And Not Exists (Select Null From TruyenNhan.dbo.DongBoLog Where TenTable = 'ChungTuKho' And MaPKExp = Cast(ChungTuKho.MaPK as varchar)
And MaTruyenNhan = C.MaTruyenNhan And NhanVe = 0 And DBName = DB_NAME());
End;
go
May 17, 2012 at 4:04 am
your trigger will fire in the following way
if records are instered or update where isNull(ChungTuKho.TenForm,'')<>'DieuChuyenKho') then perform block of code 1
else perform block of code 2
if it's not firing then i suggest it because you have performed an insert or update where isNull(ChungTuKho.TenForm,'')='DieuChuyenKho') for all records
how are you determining that the trigger did not fire ? - have you run a profiler trace to determine the data that was inserted?
can you post the data that was inserted?
MVDBA
May 17, 2012 at 4:12 am
how are you determining that the trigger did not fire ? - have you run a profiler trace to determine the data that was inserted?
No! if I action insert,update then my trigger was fired. I thing when insert/update or delete with a big rows then some rows of ChungTuKho did not fire. if SQL Server 2005 error?
can you post the data that was inserted?
I wil post later
Thank!
May 17, 2012 at 4:24 am
nguyenhoang 18993 (5/17/2012)
how are you determining that the trigger did not fire ? - have you run a profiler trace to determine the data that was inserted?No! if I action insert,update then my trigger was fired. I thing when insert/update or delete with a big rows then some rows of ChungTuKho did not fire. if SQL Server 2005 error?
can you post the data that was inserted?
I wil post later
Thank!
if some rows are processed then the trigger DID fire - more than likely one of the joins, exists or where clauses are removing rows from the resultset - you need to investigate these
MVDBA
May 17, 2012 at 6:33 am
nguyenhoang 18993 (5/17/2012)
how are you determining that the trigger did not fire ? - have you run a profiler trace to determine the data that was inserted?No! if I action insert,update then my trigger was fired. I thing when insert/update or delete with a big rows then some rows of ChungTuKho did not fire. if SQL Server 2005 error?
can you post the data that was inserted?
I wil post later
Thank!
1. I can assure you that the trigger you have always fires! Once per INSERT, UPDATE or DELETE action
2. You have results not matching your expectation. It is all and only to do with the logic you have in a trigger.
To help you further, you will need to provide more details as per "Forum Etiquette" must-read article. Link at the bottom of my signature.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply