Rowcount in 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

SQL Server Development (2000)

Rowcount in Trigger

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-07-13 : 19:46:10

Guys,
I have the follwing trigger which works fine if I do not include the following clause
"
SELECT @ROWCOUNT=COUNT(*) FROM USERS
IF (@ROWCOUNT <> 0)
"

What I try to do with this clause is to make sure that trigger inserts in userhist table if only the
users table is not empty hence I try to check the rowcount in the if clause.
But it seems to error out. Is there any better way doing this check.

CREATE TRIGGER [USER_BD] ON [dbo].[USERs]
FOR DELETE
AS
DECLARE
@USERID varchar (50),
@APP varchar (50) ,
@LOGON datetime,
@ROWCOUNT INT

SELECT @ROWCOUNT=COUNT(*) FROM USERS
IF (@ROWCOUNT <> 0)

BEGIN
SELECT
@USERID = USERID,
@APP = APP,
@LOGON = LOGON
FROM deleted
insert into USERHIST (

USERID,
APP,
LOGON

)
VALUES(
@USERID,
@APP,
@LOGON
)
end

Any suggestions/input is appreciated

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-13 : 19:51:24

You have a fundamental problem with your trigger. Please see this for more information:
http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

All you need is this:

IF EXISTS(SELECT * FROM USERS)
INSERT INTO USERHIST (USERID,APP,LOGON)
SELECT USERID,APP,LOGON
FROM deleted

And yes SELECT * should be used for EXISTS and NOT EXISTS as it's fastest in this case only.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources