我如何使一个触发器工作与两个源表?

Question

我有麻烦让这个触发器正常工作。我很少使用触发器，所以我对它们的理解可能有点偏离，因为我编写这个代码的方式似乎应该工作(至少对我来说)，但事实并非如此。如果对tblCollateralAssignment进行了更新，则触发器将正确工作并保存tblcollateralassignmenthhistory表中的所有信息(包括来自CustodianData表的任何信息)。

但是，如果更新只触及了CustodianData表(例如，只更新了CustodianFrom字段)，而没有触及tblcollateralassignment中的字段，则触发器不起作用，更新不会保存到tblcollateralassignmenthhistory。

希望我只是忽略了一件小事，但我一直盯着它，我不知所措。

ALTER TRIGGER [dbo].[Trigger_tblCollateralAssignmentToHistory]
ON [dbo].[tblCollateralAssignment] AFTER UPDATE
AS 
SET NOCOUNT ON;
IF EXISTS ( SELECT ChainID FROM inserted ) AND EXISTS ( SELECT ChainID FROM deleted )
BEGIN

--This updates history with the changed records.
INSERT INTO dbo.tblCollateralAssignmentHistory
SELECT i.ChainID
,d.LoanNo
,d.FileDate as BeginFileDate
,i.FileDate as EndFileDate
,d.ModifiedBy
,d.RecordSource
,d.AssignmentNo
,d.AssignmentFrom
,d.AssignmentTo
,d.RecordedDate
,d.StatusPerTitle
,d.RecordedImage
,d.AtCustodian
,d.AOMComments
,d.IsVisible
,d.DocXNotarized
,d.AOMBook
,d.AOMDocumentNo
,d.AOMPage
,d.CorrectiveAssignment
,d.DocumentID
,d.DocumentDetails
,cd.CustodianUID
,cd.CustodianDocCode AS DocCode
,d.MASIdentified
,cd.CustodianExceptionDescription AS ExceptionDescription
,cd.CustodianFrom
,cd.CustodianTo
,cd.CustodianNotation
,cd.CustodianComments
FROM deleted as d
JOIN inserted as i on i.ChainID = d.ChainID
LEFT JOIN ExternalReports.CustodianData AS cd ON cd.ChainId = i.ChainID
END
--Clear out CorrectiveAssignment if the related ChainID IsVisible is set to false.  This is a trigger enacted cascade delete for soft deleted chains.  Edited records are also copied to history.
DECLARE @FileDate datetime = GETDATE();
UPDATE 
dbo.tblCollateralAssignment
SET 
CorrectiveAssignment = NULL,
FileDate = @FileDate
OUTPUT  
inserted.ChainID,
deleted.LoanNo,
deleted.FileDate AS BeginFileDate,
@FileDate AS EndFileDate,
deleted.ModifiedBy,
deleted.RecordSource,
deleted.AssignmentNo,
deleted.AssignmentFrom,
deleted.AssignmentTo,
deleted.RecordedDate,
deleted.StatusPerTitle,
deleted.RecordedImage,
deleted.AtCustodian,
deleted.AOMComments,
deleted.IsVisible,
deleted.DocXNotarized,
deleted.AOMBook,
deleted.AOMDocumentNo,
deleted.AOMPage,
deleted.CorrectiveAssignment,
deleted.DocumentID,
deleted.DocumentDetails,
cd.CustodianUID,
cd.CustodianDocCode AS DocCode,
deleted.MASIdentified,
cd.CustodianExceptionDescription AS ExceptionDescription,
cd.CustodianFrom,
cd.CustodianTo,
cd.CustodianNotation,
cd.CustodianComments    
INTO 
dbo.tblCollateralAssignmentHistory
FROM 
dbo.tblCollateralAssignment AS CorrectiveAssignment
LEFT JOIN
ExternalReports.CustodianData AS cd ON cd.ChainId = CorrectiveAssignment.ChainID
WHERE EXISTS 
(select * from dbo.tblCollateralAssignment as DeletedAssignment where DeletedAssignment.IsVisible = 0 and DeletedAssignment.ChainID = CorrectiveAssignment.CorrectiveAssignment)
AND 
CorrectiveAssignment is not null
AND EXISTS
(select * from inserted AS i where i.LoanNo = CorrectiveAssignment.LoanNo);

Answer 1

希望这能奏效

CREATE OR REPLACE TRIGGER checkDuration
BEFORE INSERT OR UPDATE on offering
FOR EACH ROW
DECLARE
isFound NUMBER;
BEGIN
SELECT 1 INTO isFound FROM DUAL WHERE EXISTS (
SELECT * FROM Course c
WHERE c.courseId = :new.courseId AND c.duration = 5);
IF EXTRACT(MONTH FROM :new.startDate) = 12 
THEN RAISE_APPLICATION_ERROR(-20001, 'Courses of five days duration cannot be run in December');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;