USE [XXX]
GO
/****** Object: Trigger [dbo].[TRI_MailBuchung] Script Date: 16.01.2024 21:12:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TRI_MailBuchung]
ON [dbo].[KHKLagerplatzbuchungen]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
--Inhalt für die Email definieren
DECLARE @tableHTML NVARCHAR(MAX);
set @tableHTML = concat(
'Job: '
,(select i.Job From inserted as i)
, '<br>BuchungsID: '
,(select i.BuchungsID From inserted as i)
,'<br>Artikelnummer: '
,(select i.Artikelnummer From inserted as i)
,'<br>Benutzer: '
, ( select lj.Benutzer
FROM KHKLagerplatzbuchungenJobs as LJ
left join inserted
on inserted.job=lj.Job
WHERE lj.job=inserted.job
)
,'<br>Bewegungsdatum: '
,(select i.Bewegungsdatum from inserted as i)
,'<br>Bewegungsart: '
,(select i.Bewegungsart from inserted as i)
,'<br> Memo: '
,( select lj.Memo
FROM KHKLagerplatzbuchungenJobs as LJ
left join inserted
on inserted.job=lj.Job
WHERE lj.job=inserted.job
)
,'<br> Standardtext: '
,(select lj.Standardtext
FROM KHKLagerplatzbuchungenJobs as LJ
left join inserted
on inserted.job=lj.Job
WHERE lj.job=inserted.job
)
)
-- Email versenden
DECLARE @empfaenger varchar(255) = 'XXX@XXX.de';
DECLARE @mail_subject varchar(255) = 'Warenbewegung für nicht erlaubten Zeitraum';
IF EXISTS(SELECT * FROM inserted as i WHERE YEAR(i.Bewegungsdatum) <> YEAR(GETDATE()))
BEGIN
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'SQL DBA',
@recipients = @empfaenger,
@subject = @mail_subject,
@body = @tableHtml,
@body_format = 'HTML';
END
END
GO
ALTER TABLE [dbo].[KHKLagerplatzbuchungen] ENABLE TRIGGER [TRI_MailBuchung]
GO