Apologies for the alignment im new so having some trouble. Hi guys I'm learning SQL server for the first time and the concept of the trigger is a little tricky. I missed my update trigger and now having trouble implementing them practically. coming to the question we have two tables one is

    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

Question 1 When a Transaction is altered, the difference between the old amount and the new amount cannot be more than 10%, if the transaction has been affected by the teller

Question 2 More than three Cash Withdrawal transactions in a single account on the same day should not be allowed

My code which i tried

I tried some logic but it seems not working. Can some one help me with the logic.

    USE [casestudy]

GO

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

Is this query correct to get whether the transaction was done by the teller or not?*/ end can someone help me with these two triggers' logic? I'm trying but I'm not able to get the logic.

CodePudding user response：

You have a number of serious bugs in your trigger

• inserted and deleted can contain multiple rows. You need to join them based on the primary key
• Instead of PRINT and ROLLBACK use THROW
• You also need to add the trigger for INSERT
• money is a bad data type for most purposes, due to rounding. Use decimal instead.

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

For this trigger to work efficiently you are going to want an index on

TransactionMaster (ACID, [DATE OF TRANSACTION])

I recommend you don't use column names that need quoting with [], it's really annoying.