CREATE TRIGGER [dbo].[MailReturnCBRHandling] ON dbo.ACC AFTER UPDATE AS
Declare @v_Agency_ID varchar(15), @n_SaleID numeric, @n_DebtorID numeric, @n_AccNo numeric, @v_AccStatID varchar(20), @b_AccStatMailReturned bit, @b_AccStatReportsToCBR bit, @b_DebtorMailReturned bit, @b_AccReportsToCBR bit
IF UPDATE(Stat_ID)
BEGIN
CREATE TRIGGER [dbo].[MailReturnCBRHandling] ON dbo.ACC
AFTER UPDATE
AS
Declare
@v_Agency_ID varchar(15),
@n_SaleID numeric,
@n_DebtorID numeric,
@n_AccNo numeric,
@v_AccStatID varchar(20),
@b_AccStatMailReturned bit,
@b_AccStatReportsToCBR bit,
@b_DebtorMailReturned bit,
@b_AccReportsToCBR bit
IF UPDATE(Stat_ID)
BEGIN
DECLARE c_InsertedMailReturnCBR CURSOR FOR
SELECT Agency_ID, SaleID , Debtor_ID, AccNo , Stat_ID, IsNull(IsBureau,0)
FROM INSERTED
OPEN c_InsertedMailReturnCBR
FETCH NEXT FROM c_InsertedMailReturnCBR INTO @v_Agency_ID, @n_SaleID, @n_DebtorID, @n_AccNo, @v_AccStatID, @b_AccReportsToCBR
WHILE @@FETCH_STATUS = 0
BEGIN
-- ************************************************* Get AccStat MailReturned & ReportsToCBR *************************************************
DECLARE c_AccStat CURSOR FOR
SELECT MailReturned , ReportsToCBR FROM AccStat WHERE Agency_ID = @v_Agency_ID AND AccStat = @v_AccStatID
OPEN c_AccStat
FETCH NEXT FROM c_AccStat INTO @b_AccStatMailReturned, @b_AccStatReportsToCBR
-- ************************************************* Get Debtor MailReturned *************************************************
DECLARE c_Debtor CURSOR FOR
SELECT IsNull(MailReturned,0) FROM Debtor WHERE Agency_ID = @v_Agency_ID AND Debtor_ID = @n_DebtorID
OPEN c_Debtor
FETCH NEXT FROM c_Debtor INTO @b_DebtorMailReturned
-- ************************************************* UPDATE MailReturned *************************************************
IF @b_AccStatMailReturned IS NOT NULL
BEGIN
IF @b_AccStatMailReturned = 1 AND @b_DebtorMailReturned = 0
BEGIN
UPDATE Debtor SET MailReturned = 1 WHERE Debtor_ID = @n_DebtorID
INSERT INTO DebtorNote (AGENCY_ID, DEBTOR_ID, NOTE, Priority, StampTime, Supress)
VALUES (@v_Agency_ID, @n_DebtorID, 'Auto Generated Note --> Debtor Mail Returned changed from: False To: True','C', GetDate() ,0)
END
IF @b_AccStatMailReturned = 0 AND @b_DebtorMailReturned = 1
BEGIN
UPDATE Debtor SET MailReturned = 0 WHERE Debtor_ID = @n_DebtorID
INSERT INTO DebtorNote (AGENCY_ID, DEBTOR_ID, NOTE, Priority, StampTime, Supress)
VALUES (@v_Agency_ID, @n_DebtorID, 'Auto Generated Note --> Debtor Mail Returned changed from: True To: False','C', GetDate() ,0)
END
END
-- ************************************************* UPDATE ReportsToCBR *************************************************
IF @b_AccStatReportsToCBR IS NOT NULL
BEGIN
IF @b_AccStatReportsToCBR = 1 AND @b_AccReportsToCBR = 0
BEGIN
UPDATE ACC SET IsBureau = 1 WHERE AccNo = @n_AccNo
INSERT INTO DebtorNote (AGENCY_ID, DEBTOR_ID, NOTE, Priority, StampTime, Supress,AccNo,SaleID)
VALUES (@v_Agency_ID, @n_DebtorID, 'Auto Generated Note --> Reports To Bureau changed from: False To: True','C', GetDate() , 0, @n_AccNo, @n_SaleID)
END
IF @b_AccStatReportsToCBR = 0 AND @b_AccReportsToCBR = 1
BEGIN
UPDATE ACC SET IsBureau = 0 WHERE AccNo = @n_AccNo
INSERT INTO DebtorNote (AGENCY_ID, DEBTOR_ID, NOTE, Priority, StampTime, Supress,AccNo,SaleID)
VALUES (@v_Agency_ID, @n_DebtorID, 'Auto Generated Note --> Reports To Bureau changed from: True To: False','C', GetDate() , 0, @n_AccNo, @n_SaleID)
END
END
FETCH NEXT FROM c_InsertedMailReturnCBR INTO @v_Agency_ID, @n_SaleID, @n_DebtorID, @n_AccNo, @v_AccStatID, @b_AccReportsToCBR
CLOSE c_Debtor
DEALLOCATE c_Debtor
CLOSE c_AccStat
DEALLOCATE c_AccStat
END
CLOSE c_InsertedMailReturnCBR
DEALLOCATE c_InsertedMailReturnCBR
END
END
The trigger is written by the company that provides us with the software that we use to run out business. There is no need to create it because it already exists. It should instantly fail because an object of that name already exists. The
The vendor provided this software runs the entire application logged in as SA. I have begged my management to make them run all sql under the windows credential of the logged in user but they claim that they have no way to force the software vendor to to that so the SQL process list does not tell me anything about who submitted the create trigger and under what circumstances. WeWe are running SQL Server 2016 SP3. I
I am wondering 1) Why would this suspend? Why not just return an error tha t says an object of that name already exists? 2) Given that it has suspended, why do all the other tasks back up behind it? Put another way, what resource is this process holding that the others need. I:
Why would this suspend? Why not just return an error tha t says an object of that name already exists?
Given that it has suspended, why do all the other tasks back up behind it? Put another way, what resource is this process holding that the others need.
I can't believe that they are all waiting to create or alter triggers or that something that this process needs to change is in a page that everyone else would want. It would be as if this process was able to put a lock on the entire database, but why? This
This happens about every two weeks. Exact same trigger. I have not yet determined a set of circumstances that trigger it. Thank you.
