Newby - Stuck with a trigger.

Question

Post reply

Newby - Stuck with a trigger.

Private102

Valued Member

Points: 62
More actions
November 23, 2023 at 7:37 pm

#4333255

Hello Forum,
Apologies for this post, I'm stuck with an update trigger that i need to amend. I need to capture the Date, OrderID and CustID, Surname and Firstname from a trigger which prevents an update on table.
The Date, OrderID and CustID , Surname and Firstname need to go into a table. I can't for the life of me work it out. Any help would be appreciated.

Here's the trigger.
CREATE TRIGGER [dbo].[tr_ORDERID]
ON [dbo].[MYCUSTOMERS]
FOR UPDATE
AS
BEGIN
IF(UPDATE(ORDERID))
BEGIN
DECLARE @INSCUSTID INT, @INSORDERID int, @OLDORDERID INT, @DELORDERID INT, @SURNAME varchar(20), @FIRSTNAME varchar(20)
SET @DELORDERID = (select ORDERID from deleted)
SET @INSCUSTID = (select custid from inserted)
--SET @INSORDERID = (select ORDERID from inserted)
SET @INSORDERID = (select ORDERID from MYCUSTOMERS where CUSTID = @INSCUSTID)
SET @SURNAME = (select SURNAMEfrom MYCUSTOMERS where CUSTID = @INSCUSTID)
SET @FIRSTNAME = (select FIRSTNAME from MYCUSTOMERS where CUSTID = @INSCUSTID)

IF @DELORDERID in (25) and @INSORDERID NOT IN (25)
or @DELORDERID in (25) and @INSORDERID is NULL
BEGIN
-- rollback but I need to capture the data into another table.
UPDATE MYCUSTOMERS set ORDERID = @DELORDERID where CUSTID = @INSCUSTID
-- This is where I am stuck.

SELECT
ORDERID,
getdate(),
custid,
ORDERID
FROM
INSERTED
END;
END
END
GO

Any help would be most appreciated.

Nicky.

November 24, 2023 at 2:45 pm

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18842 More actions · Answer 1

This post makes no sense. Why would you have an OrderId in a Customer table!?

Also there is no DDL, including the primary key, so the best that can be done is a couple of pointers.

Do NOT have variables in a trigger! They need to be set based.
You do not want to ROLLBACK in a trigger. Use an INSTEAD OF trigger and only UPDATE under the required conditions.

ScottPletcher SSC Guru Points: 101096 More actions · Answer 2

This is somewhat confusing, but something like this would likely do what you want to do:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER [dbo].[tr_ORDERID]
ON [dbo].[MYCUSTOMERS]
AFTER UPDATE
AS
SET NOCOUNT ON;
BEGIN
IF UPDATE(ORDERID)
BEGIN
    --undo UPDATE of ORDERID
    UPDATE M
    SET ORDERID = d.ORDERID
    FROM dbo.MYCUSTOMERS M
    INNER JOIN deleted d ON d.custid = M.custid AND d.ORDERID = 25
    INNER JOIN inserted i ON i.custid = M.custid AND (i.ORDERID <> 25 OR i.ORDERID IS NULL)
    --
    INSERT INTO dbo.capturetable ( ... )
    SELECT i.custid, ...
    FROM dbo.MYCUSTOMERS M
    INNER JOIN deleted d ON d.custid = M.custid AND d.ORDERID = 25
    INNER JOIN inserted i ON i.custid = M.custid AND (i.ORDERID <> 25 OR i.ORDERID IS NULL)
END /*IF*/
/*end of trigger*/
GO

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".