How to write conditions for bit field of SQL server??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to write conditions for bit field of SQL server???

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-20 : 07:13:38

Hitesh Patel writes "I have some problem with comparing bit values.

Assume, I created one trigger for one table. I added ONE field with bit datatype. I have to execute some part of the trigger when its value is 1 and other part if it is 0 or NULL.

BUT my condtions are not working...my trigger code is as follows.

Declare @tempvarFlg int
Declare @tempvar int
Declare @tempInactive int
select @tempInactive = Inactive,@tempvarFlg = TriggerFlag from inserted as tblSupplierFeed

IF @tempvarFlg <> 1
begin
update tblSupplierFeed SET Inactive = 1
end

NOTE:- "TriggerFlag " has datatype as BIT.

Above update statment should be executed only when bit field's value is 0 or NULL.

BUT anyhow when its value is 0 or NULL although it's not working.

I shall be grateful to you.

Thanks,
Hitesh Patel."

Kristen
Test

22859 Posts

Posted - 2005-01-20 : 07:50:17

Your trigger is designed to work with only one record. However, the trigger will be called on the SET of records being changed - i.e. "inserted" table will store records for ALL records being changed

Thus

select @tempInactive = Inactive,@tempvarFlg = TriggerFlag from inserted as tblSupplierFeed

wil set the variables to the last record found in inserted (using NO PARTICULAR order!)

Also

update tblSupplierFeed SET Inactive = 1

will set EVERY ROW in the tblSupplierFeed table (this may be what you want, but I doubt it!)

Lastly, you mention "when its value is 0 or NULL although it's not working"

IF @tempvarFlg <> 1

will be TRUE if @tempvarFlg = 0. However it will be FALSE when @tempvarFlg is NULL. (NULL never equals anything, not even another NULL [unless you use non-standard database settings to force that behaviour]

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources