Help On Trigger - 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

Transact-SQL (2000)

Help On Trigger

Author

Topic

BigRetina
Posting Yak Master

144 Posts

Posted - 2002-09-12 : 08:37:27

Salute..
I have this strange problem on my trigger..In the first version everything works fine..If i try to insert a record with a wrong year it will RAISERROR fine!..
In the second version..I just add code before that to know what type of data operation took palace..that is ..update,delete or insert..it is working fine and it is entering the block ok..when it REACHES the RAISERROR statement this time it passes it as if nothing is wrong and it does not RAISE ERROR..i know it is reaching that statement and passing it fine..i used echo and xp_cmdshell to verify that!!
WHAT IS THE DIFFERENCE BETWEEN THE TWO VERSIONS!??
I AM STUCK!!..PLEASE HELP ME .. Thanks In Advance!

**********FIRST VERSION
ALTER TRIGGER trgGLR_TransH
ON dbo.GLR_TransH
FOR INSERT, UPDATE, DELETE
AS
Declare @VoucherDate smalldatetime,@FiscalYear int,@TempYear int
Select @VoucherDate=VoucherDate,@FiscalYear=FiscalYear From Inserted
If @@RowCount<>1
Select @TempYear=0
else
Select @TempYear=Year(@VoucherDate)
If @TempYear <> @FiscalYear and @TempYear <> 0
Begin
Raiserror('Year not Valid',16,-1)
Rollback
End

**********SECOND VERSION
ALTER TRIGGER trgGLR_TransH
ON dbo.GLR_TransH
FOR INSERT, UPDATE, DELETE
AS
Declare @UpdateType as Char(3),@UpdateCounter As int,@ErrOccur as smallint
Set @UpdateType=' '
Set @UpdateCounter=0
Set @ErrOccur=0

Select TransHId from Deleted
if @@RowCount >= 1
Begin
Select @UpdateType='DEL'
Set @UpdateCounter=@UpdateCounter + 1
End
Select * from Inserted
If @@RowCount >= 1
Begin
Select @UpdateType='INS'
Set @UpdateCounter=@UpdateCounter+1
End
If @UpdateCounter=2
Select @UpdateType='UPD'
Declare @TransHId int
IF @UpdateType='INS' OR @UpdateType='UPD'
Begin
Declare @VoucherDate smalldatetime,@FiscalYear int,@TempYear int
Select @VoucherDate=VoucherDate,@FiscalYear=FiscalYear From Inserted
If @@RowCount<>1
Select @TempYear=0
Else
Select @TempYear=Year(@VoucherDate)
If @TempYear <> @FiscalYear and @TempYear <> 0
Begin
Raiserror('Year not Valid',16,-1)
Rollback
End

End

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-09-12 : 09:10:06

Hmmm...
I'm a little bit confused looking at your code, but I suggest that you break this "one" trigger into three separate (for every operation a trigger) and get rid of the IF's.

I'm still not sure if I got it right, but it seems that INSTEAD OF triggers would come handy.

BigRetina
Posting Yak Master

144 Posts

Posted - 2002-09-12 : 09:15:14

well it works anyways in version 1..and IT SEEMS that RAISERROR is not working AT ALL after the code added in version 2..no matter WHERE!
what could be wrong??

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-12 : 09:21:06

Why not just use version 1, but only fire the trigger on INSERT and UPDATE ... you don't seem to be doing anything on DELETE anyway, right?

Jay White
{0}

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-09-12 : 09:21:46

The code you added in version 2 has a nested IF. Check your IF's and END's.

I still think you should break it up.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-12 : 09:24:54

if exists (select * from deleted) and exists (select * from deleted)
set @UpdateType='UPD'
else
if exists (select * from deleted)
set @UpdateType='DEL'
else
if exists (select * from inserted)
set @UpdateType='DEL'
else
set @UpdateType=' '

Declare @VoucherDate smalldatetime,@FiscalYear int,@TempYear int
IF @UpdateType in ('INS','UPD')
begin
if 1 <> (select count(*) From Inserted)
Select @TempYear=0, @FiscalYear=0
else
Select @TempYear=Year(@VoucherDate), @FiscalYear=FiscalYear From Inserted

If @TempYear <> @FiscalYear and @TempYear <> 0
Begin
Raiserror('Year not Valid',16,-1)
Rollback
End
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BigRetina
Posting Yak Master

144 Posts

Posted - 2002-09-12 : 09:28:42

nr..thanks..
BUT i still want to know what is wrong with my code??..
the block u added to find the type of the operation already WORKS in my code!!..and the code is REACHING the raiserror BUT NOT EXECUTING IT!!
I REDUCED VERSION 2 to the following and THE RAISERROR DIDNT WORK!!!

**********SECOND VERSION
ALTER TRIGGER trgGLR_TransH
ON dbo.GLR_TransH
FOR INSERT, UPDATE, DELETE
AS
Declare @UpdateType as Char(3),@UpdateCounter As int, @ErrOccur as smallint
Set @UpdateType=' '
Set @UpdateCounter=0
Set @ErrOccur=0

Select TransHId from Deleted
if @@RowCount >= 1
Begin
Select @UpdateType='DEL'
Set @UpdateCounter=@UpdateCounter + 1
End

Select * from Inserted
If @@RowCount >= 1
Begin
Select @UpdateType='INS'
Set @UpdateCounter=@UpdateCounter+1
End

If @UpdateCounter=2
Select @UpdateType='UPD'

RAISERROR('err',16,-1)
ROLLBACK

Edited by - BigRetina on 09/12/2002 09:30:02

Edited by - BigRetina on 09/12/2002 09:37:55

Edited by - BigRetina on 09/12/2002 09:38:25

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-12 : 10:21:21

How are you trying to detect the raiserror?
If it is in a front end it probably won't be received untill you have cleared all the result sets produced by the selects (that's one reason for replacing them).

Does the rollback work?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BigRetina
Posting Yak Master

144 Posts

Posted - 2002-09-12 : 12:53:33

I am detecting it by using the following statements..
SET @Var = @Var + '1'
SET @Cmd = 'echo ' + @Var + ' >> C:\Test.txt'
EXECUTE xp_cmdshell @Cmd

I put these lines before and after the suspect ( that sounds spooky..lol) and before and after the RAISERROR and ROLLBACK..
it passes by them ALL..but raiserro DOES NOT WORK!!.

In version ONE the raiserror works fine and my VB6 front end receives the error message fine!!..THIS IS REALLY STRANGE!!..
why not a BUG?..(oh god!!)

BigRetina
Posting Yak Master

144 Posts

Posted - 2002-09-15 : 04:27:41

Hi Again!
When I perform the same operation from query analyzer everything works just fine..I also tried it from VB.NET and everything worked fine!..it seems that it is a VB6 problem!..but what is it?..and why is the SECOND VERSION working in VB6??..the problem is front end but the two code versions are back end SQL SERVER 7.0 SP3.
I ALSO reduced VERSION TWO to the following(JUST TWO LINES!!!!)

SELECT * FROM INSERTED
RAISERROR('Error',16,-1)

IT DIDNT WORK!..
WHEN I REMOVE THE SELECT IT WORKS!!..
AMAZING!!
Please I need your help on this one!
Thanks In Advance

Edited by - BigRetina on 09/15/2002 07:54:27

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources