如何根据需求编写更新触发器

Question

为新的对齐表示歉意，所以有一些麻烦。嗨，伙计们，我第一次学习SQL服务器，触发器的概念有点棘手。我错过了我的更新触发器，现在在实际实现它们时遇到了困难。至于问题，我们有两张桌子，一张是

    UserMaster
    
    Column Name     Data Type      Remarks
    USERID          INTEGER        Primary Key
    USER NAME       VARCHAR(30)    NOT NULL
    DESIGNATION     CHAR(1)        ‘M’ for ‘MANAGER’, ‘T’ for ‘TELLER’, ‘C’ for ‘CLERK’, ‘O’ for ‘OFFICER’; NOT NULL.

TransactioMaster

Column Name         Data Type      Remarks
TRANSACTION NUMBER  INTEGER        Primary Key; Identity, seed=1, Increment=1; 
DATE OF TRANSACTION DATETIME       NOT NULL
ACID                INTEGER        Foreign Key; NOT NULL
BRID                CHAR(3)        Foreign Key; NOT NULL
TXN_TYPE            CHAR(3)        ‘CW’ for ‘CASH WITHDRAWAL’, ‘CD’ for ‘CASH 
                                   DEPOSIT’, ‘CQD’ for ‘CHEQUE DEPOSIT’; NOT NULL 
CHQ_NO              INTEGER        NULL ALLOWED
CHQ_DATE            SMALLDATETIME  NULL ALLOWED
TXN_AMOUNT          MONEY          NOT NULL
USERID              INTEGER        Foreign Key; NOT NULL

问题1当交易被更改时，如果交易受到出纳员的影响，旧金额与新金额之间的差额不得超过10%。

问题2不得在同一天在一个帐户内进行三次以上提款交易。

我试过的代码

我尝试了一些逻辑，但似乎行不通。有人能帮我弄清楚逻辑吗。

    USE [casestudy]

去

ALTER trigger [dbo].[UDT_transaction_altered]
on [dbo].[TransactionMaster]
after update
as 
begin
declare @old_userid int
declare @new_userid int
declare @acid int
declare @newamt money
declare @oldamt money
declare @diffamt money
declare @10perct money
declare @designation char(1)

--Get teller info and txn_amount Info
select @old_userid = userid, @oldamt = TXN_AMOUNT from deleted --contains old data
select @acid = acid, @new_userid = userid, @newamt = TXN_AMOUNT from inserted --contains new data


set @diffamt = @newamt - @oldamt
set @10perct = (0.1 * @oldamt) + @oldamt

if update(TXN_AMOUNT)
    begin
        if (@diffamt < 0)
            begin 
                print ' TXN_AMOUNT canot be negative' 
                print ' Transaction declined'
                rollback
            end
            if(@newamt > @10perct)
            begin
                print ' New TXN_AMOUNT canot be more than 10% of old amount' 
                print ' Transaction declined'
                rollback
            end
            else
            begin
                update TransactionMaster set TXN_AMOUNT = @newamt where acid = @acid
            end
    end
end

    

/* select TransactionNumber, acid , tm.USERID,designation ,count(*) as nooftrans from TransactionMaster tm join usermaster um
on tm.userid = um.USERID
where datediff(dd,dot,getdate()) = 0 and um.designation = 'T'
group by TransactionNumber, ACID,tm.USERID ,Designation
order by ACID

这个查询是否正确，以获得事务是否由出纳员完成？*/ end，有人能帮助我处理这两个触发器的逻辑吗？我在努力，但我无法理解逻辑。

Answer 1

你的扳机里有很多严重的错误

• inserted和deleted可以包含多个行。您需要基于主键加入它们，而不是使用THROW
• You和PRINT，ROLLBACK也需要添加触发器，因为INSERT
• money是一种糟糕的数据类型，因为大多数情况下是舍入造成的。使用decimal代替.

CREATE OR ALTER trigger dbo.UDT_transaction_altered
ON dbo.TransactionMaster
AFTER INSERT, UPDATE
AS

SET NOCOUNT ON;  -- prevent spurious resultsets
    
IF UPDATE(TXN_AMOUNT)  -- only checks for presence in statement, still need to check values
  AND EXISTS (SELECT 1
        FROM deleted d
        JOIN inserted i ON i.[TRANSACTION NUMBER] = d.[TRANSACTION NUMBER] -- join on PK
        WHERE i.TXN_AMOUNT NOT BETWEEN d.TXN_AMOUNT AND d.TXN_AMOUNT * 1.1)
BEGIN    
    THROW 50001, 'TXN_AMOUNT canot be negative or more than 10% of old amount', 1;
END;

IF (UPDATE(ACID)  -- only checks for presence in statement, still need to check values
    OR UPDATE([DATE OF TRANSACTION]))
  AND EXISTS (SELECT 1
    FROM inserted i
    JOIN TransactionMaster tm
        ON tm.ACID = i.ACID
       AND tm.[DATE OF TRANSACTION] >= CAST(CAST(i.[DATE OF TRANSACTION] AS date) AS datetime)
       AND tm.[DATE OF TRANSACTION] < CAST(DATEADD(day, 1, CAST(i.[DATE OF TRANSACTION] AS date)) AS datetime)
    GROUP BY
      tm.ACID
    HAVING COUNT(DISTINCT CAST(tm.[DATE OF TRANSACTION] AS date)) > 3)
BEGIN
    THROW 50001, 'More than three transactions not allowed', 1;
END;
go

要使此触发器高效工作，您将需要一个索引。

TransactionMaster (ACID, [DATE OF TRANSACTION])

我建议您不要使用需要引用[]的列名，这真的很烦人。