USE [CHECKDB] GO /****** Object: Trigger [dbo].[tr_tblIslemKodINSERT] Script Date: 06/07/2013 10:07:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[tr_tblIslemKodINSERT] ON [dbo].[tblIslemKod] FOR INSERT AS BEGIN SET NOCOUNT ON --DECLARE @IslemAd If (SELECT IslemAd FROM INSERTED) = N'HareketTipi' INSERT INTO CHECKDB.dbo.tblIslemKodUpdatedRecods (tblIslemKod, IslemAd, IslemKod, IslemAciklama, IslemDetay) SELECT tblIslemKod, IslemAd, IslemKod, IslemAciklama, IslemDetay FROM INSERTED AS I INSERT INTO CHECKDB.dbo.tblHareketTipleri (Aciklama,RaporTipi, Detay) SELECT IslemAciklama, 'M', IslemDetay FROM INSERTED AS I DECLARE @svrName VARCHAR(255) DECLARE @IP VARCHAR(100) DECLARE @subject1 varchar(200) SET @svrName = @@SERVERNAME SET @IP=convert(varchar,CONNECTIONPROPERTY('local_net_address')) SET @subject1=@IP+'_'+@svrName+' tblIslemKod Tablosuna kayit eklendi' begin --Query sonucunu mail atmasi için html formatta tablo olusturma DECLARE @tableHTML NVARCHAR(MAX) ; SET @tableHTML = N'
tblHareketTipleri Tablosu Guncellendi
' + N'
' + N'
HareketTipiId' + N'
Aciklama' + N'
RaporTipi' + N'
Detay' + N'
RecordDate' + CAST ( ( SELECT TOP 100 td = [HareketTipiId] , '', td = [Aciklama], '', td = [RaporTipi], '', td = [Detay], '', td = [RecordDate], '' FROM [CHECKDB].[dbo].[tblHareketTipleri] WHERE [RecordDate] > GETDATE() -1 FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'
'+ N' '+ N'
' + N'
Detay icin tblIslemKodUpdatedRecods tablosunda bakabilirsiniz
'; --Sayet olu?turulan tablo ile asagidaki kosul saglanmyyorsa mail atmyyor SELECT top 1 * FROM [CHECKDB].[dbo].[tblHareketTipleri] WHERE [RecordDate] > GETDATE() -1 if @@rowcount>0 begin --query sonucunu mail atan bölüm EXEC msdb.dbo.sp_send_dbmail --@recipients='[email protected]', @recipients='[email protected]', @subject = @subject1, @body = @tableHTML, @body_format = 'HTML'; end end END
