help with trigger

[croiva25]

I hope somebody can help me, I have inserted the code for the trigger that if/when dates are changed it sends an email like below, but instead of noting that the field has changed and emailing that I need to email and capture the actual date it was set to and I am not sure how to do this.
Could somebody help me with that

As you can see the two bottom parts of the trigger code store the data into the appropriate table and then email it.


"Dates Altered for Test Record:

Inspection Date

Supplier: Test Pty Ltd

ID: 28"



DECLARE @Dates VARCHAR(512)
DECLARE @Email VARCHAR(255)
DECLARE @RelocateID INT
DECLARE @RemovalistNumber INT
DECLARE @InspectionDate DATETIME

SET @Dates=''

DECLARE getRecs CURSOR FOR SELECT a.RelocateID, a.RemovalistNumber
FROM INSERTED a

OPEN getRecs

FETCH NEXT FROM getRecs INTO @RelocateId, @RemovalistNumber

WHILE (@@FETCH_STATUS <> -1)
BEGIN

SET @Dates=''

/* Inspection Date */
IF EXISTS(SELECT * FROM INSERTED a
INNER JOIN DELETED b ON (a.RelocateID=b.RelocateID AND a.RemovalistNumber=b.RemovalistNumber)
WHERE ISNULL(CONVERT(VARCHAR, a.InspectionDate, 106), '') <>
ISNULL(CONVERT(VARCHAR, b.InspectionDate, 106), '')
AND a.RelocateID=@RelocateID AND a.RemovalistNumber=@RemovalistNumber )
BEGIN
IF @Dates <> ''
SELECT @Dates=@Dates + ', '
SELECT @Dates=@Dates + 'Inspection Date'

END


/* If dates have been changed */
IF @Dates <> ''
BEGIN
/* Store a note in the RelocateeNotes table */
INSERT INTO RelocateeNotes (RelocateID, DateCom, InputBy, CommentNote)
SELECT RelocateID, GETDATE(), 2351, 'Dates Altered for Test Record:' + CHAR(10) + CHAR(10) +
@Dates + CHAR(10) + CHAR(10) + ISNULL('Supplier: ' + co.Name + CHAR(10) + CHAR(10), '') +
ISNULL('ID: ' + CONVERT(VARCHAR, a.ID), '')
FROM INSERTED a LEFT JOIN Companies co ON (a.SupplierID=co.CompanyID)
WHERE RelocateID=@RelocateID AND RemovalistNumber=@RemovalistNumber

/* Store the details */
SELECT @Dates='Dates Altered for Test Record:' + CHAR(10) + CHAR(10) +
@Dates + CHAR(10) + CHAR(10) + ISNULL('Supplier: ' + co.Name + CHAR(10) + CHAR(10), '') +
ISNULL('RFQID: ' + CONVERT(VARCHAR, a.RFQID), '')
FROM INSERTED a LEFT JOIN Companies co ON (a.SupplierID=co.CompanyID)
WHERE RelocateID=@RelocateID AND RemovalistNumber=@RemovalistNumber

/* Get the managers e-mail */
SELECT @Email=Email FROM Test.dbo.Managers a INNER JOIN Test.dbo.RelocateeManagers b

ON (a.ManagerID=b.IACManagerID)
WHERE RelocateID=@RelocateID

IF @Email IS NOT NULL
EXEC Test.dbo.SendSimpleEmail @Email, 'Removalist Dates Changed', @Dates

EXEC Test.dbo.SendSimpleEmail test@test.com', 'Removalist Dates Changed', @Dates
END

FETCH NEXT FROM getRecs INTO @RelocateId, @RemovalistNumber
END

DEALLOCATE getRecs

 

[Catadmin]

Well, I'm not sure which of these columns you're storing the old information in, but you should be able to pull it based on a join with Inserted.

Work this into your code. PK stands for Primary Key. If you have another unique identifier for the rows, you could use that as well.

Insert into RelocateeNotes (NewDate)
Select (NewDate) from Table1 t1
Left Outer Join Inserted i 
on t1.PK = i.PK
Where ....

Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[croiva25]

Thanks for you reply but I don't really understand your reponse, it seems to me you think that i have a column that this new date value needs to be inserted in, but that is not the case, this "new or changed date value" needs to be part of the comment which is what this code does here. Please have in mind I am not very good with stored proc./triggers i am altering this but it was created by someone else.

/* If dates have been changed */
IF @Dates <> ''
BEGIN
/* Store a note in the RelocateeNotes table */
INSERT INTO RelocateeNotes (RelocateID, DateCom, InputBy, CommentNote)
SELECT RelocateID, GETDATE(), 2351, 'Dates Altered for Test Record:' + CHAR(10) + CHAR(10) +
@Dates + CHAR(10) + CHAR(10) + ISNULL('Supplier: ' + co.Name + CHAR(10) + CHAR(10), '') +
ISNULL('ID: ' + CONVERT(VARCHAR, a.ID), '')
FROM INSERTED a LEFT JOIN Companies co ON (a.SupplierID=co.CompanyID)
WHERE RelocateID=@RelocateID AND RemovalistNumber=@RemovalistNumber

/* Store the details */
SELECT @Dates='Dates Altered for Test Record:' + CHAR(10) + CHAR(10) +
@Dates + CHAR(10) + CHAR(10) + ISNULL('Supplier: ' + co.Name + CHAR(10) + CHAR(10), '') +
ISNULL('RFQID: ' + CONVERT(VARCHAR, a.RFQID), '')
FROM INSERTED a LEFT JOIN Companies co ON (a.SupplierID=co.CompanyID)
WHERE RelocateID=@RelocateID AND RemovalistNumber=@RemovalistNumber

Thanks