trigger problem in MSDE2000 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

trigger problem in MSDE2000

Author

Topic

san79
Starting Member

42 Posts

Posted - 2008-03-28 : 04:13:02

hi
i am having a table called allowances one of the field is used to determine the given allowance can be a basic pay or not. when once it is given 'y' then the future records should not be given 'y'. i wrote a trigger for it, but it allows me multiple entering of 'y'. i am pasting my trigger code here.


ALTER TRIGGER tblAllowances_Trigger1
ON dbo.tblAllowances
FOR insert
AS
		
	if (SELECT COUNT(*) FROM dbo.tblAllowances where IsBasicpay='y')>=1
	begin
	raiserror ('invalid',1,10)
	rollback
	end

please correct me if i have done anything silly
anticipating your valuable comments and replies.
best regards
s.an

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-28 : 04:20:12

I think you need an instead of trigger for this purpose. omething like:-

ALTER TRIGGER tblAllowances_Trigger1
ON dbo.tblAllowances
INSTEAD OF  insert
AS
		
	if (SELECT COUNT(*) FROM dbo.tblAllowances where IsBasicpay='y')=0
	begin
	INSERT INTO tblAllowances
        SELECT * FROM INSERTED
	end
GO

san79
Starting Member

42 Posts

Posted - 2008-03-29 : 10:17:30

hi thanks for the reply, it is actually do work but it dose not allow me to enter any record after a single 'y' record. simply putting there can be any number of 'n' but only one 'y' should be present.
how can i modify the sql statement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-29 : 10:34:24

[code]ALTER TRIGGER tblAllowances_Trigger1
ON dbo.tblAllowances
INSTEAD OF insert
AS

if (SELECT COUNT(*) FROM dbo.tblAllowances where IsBasicpay='y')=0 OR (SELECT IsBasicpay FROM INSERTED)='n'
begin
INSERT INTO tblAllowances
SELECT * FROM INSERTED
end
GO[/code]

san79
Starting Member

42 Posts

Posted - 2008-03-30 : 02:28:14

thanks boss that works great, and also another thing, how i indicate the user that the record is not inserted, i used a raiserror ('invalid',1,10) but the error didnt pooped out. here is the code
if (SELECT COUNT(*) FROM dbo.tblAllowances where IsBasicpay='y')=0 OR (SELECT IsBasicpay FROM INSERTED)='n'
begin
INSERT INTO tblAllowances
SELECT * FROM INSERTED
end
raiserror ('invalid'1,10)
pls note that the user input is from a vb program

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-30 : 03:08:20

More info on error handling here:-

http://www.sommarskog.se/error-handling-I.html

san79
Starting Member

42 Posts

Posted - 2008-03-31 : 03:41:25

hi
thanks for the excellent support rendered by you, it worked, the link was very useful i have to just change
if (SELECT COUNT(*) FROM dbo.tblAllowances where IsBasicpay='y')=0 OR (SELECT IsBasicpay FROM INSERTED)='n'
begin
INSERT INTO tblAllowances
SELECT * FROM INSERTED
end
else
begin
raiserror ('invalid',16,10)
rollback
return
end
i think the error priority 16 worked the magic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources