Click here to Skip to main content
15,868,054 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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

Sorry for the tables alignment i can't align them properly here

What I have tried:

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

SQL
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
Posted
Updated 21-Jul-22 2:37am
v4
Comments
Shivam Chinna 2022 21-Jul-22 2:24am    
This is not an assignment for grades. I'm a freelancer from a non-IT background learning SQL server for free from SQL server videos shared by my friend because I can't afford the training fee. in those videos, triggers are missing, and now facing trouble implementing them practically. Just learning SQL to upgrade my skills for better opportunities. Any help would be much appreciated.
Shivam Chinna 2022 21-Jul-22 3:24am    
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?
Shivam Chinna 2022 21-Jul-22 4:40am    
should we use cursors to update the result set for by the above-mentioned update trigger requirement?
CHill60 21-Jul-22 8:16am    
Highly unlikely that you would need a cursor ... for anything. There is usually a set-based way to do it

While we are more than willing to help those that are stuck, that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

If you are having problems getting started at all, then this may help: How to Write Code to Solve a Problem, A Beginner's Guide[^]
 
Share this answer
 
Quote:
learning SQL server for free from SQL server videos shared by my friend
I've not yet been able to find a video that conveys sufficient information on technical topics by itself. If you are going to use them at all, use them in conjunction with other resources, such as articles and tutorials. There are many free ones available e.g.
The Comprehensive Guide to SQL Triggers[^]

Quote:
because I can't afford the training fee.
Have you looked at cheaper alternatives such as Pluralsight, LinkedIn Learning, A book? There are also several articles here on Code Project, all free - see this site Search[^]

Quote:
but it seems not working
This is the least helpful bit of information that new members give us. It tells us exactly nothing about your problem. Be specific - is an error thrown? What values are returned (and what values were you expecting). On that note, well done for providing sample data.

I can warn you about one thing - your trigger is assuming that only one row at a time can be altered - this may not be the case - learn about Transactions and remember that SQL is a set-based language, you rarely need cursors or loops (see Processing Loops in SQL Server[^] )
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900