Update triggers - 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)

Update triggers

Author

Topic

hateface
Starting Member

3 Posts

Posted - 2004-07-16 : 09:57:00

I have is 19 columns in one table. 18 of them are bit value, 1 of them char. The 18 represent 9 pairs. Each pair represents requested and created. I wanted to write an update trigger on all 9 created fields that would check to see if ALL of the requested fields were equal to thier respective created fields and if they were, to Change the status of field 19 from active to completed.

This is for a new user account form whereby a user would request access to certain systems (administrated in diffrent areas) and the request status would stay in an active state until all of the accounts were completed by the various administrators. That way every time one administrator created their respective account the database would check and possible change the status of the request.

If you have any insight in this area it would be much appreciated. I certainly need all of the help I can get.

ButchieZ
Starting Member

7 Posts

Posted - 2004-07-16 : 12:43:02

try this

CREATE TRIGGER tr_update ON [dbo].[tblx]
FOR UPDATE
AS

Update a
set fld19 = 'completed'
FROM tblx a
INNER JOIN inserted b
ON a.id = b.id
WHERE b.fld1 = b.fld10
AND b.fld2 = b.fld11
AND b.fld3 = b.fld12
AND b.fld4 = b.fld13
AND b.fld5 = b.fld14
AND b.fld6 = b.fld15
AND b.fld7 = b.fld16
AND b.fld8 = b.fld17
AND b.fld9 = b.fld18

Kristen
Test

22859 Posts

Posted - 2004-07-17 : 03:52:38

Nice example ButchieZ

Couple of observations:

There need to be no NULL values allowed for the bit fields

ButchieZ example matches what you described, but I wonder if "completed" is signified by all bits being ONE, rather than EQUAL? in whcih case you would need
WHERE b.fld1 = 1
AND b.fld10 = 1
AND b.fld2 = 1
AND b.fld11 = 1
...

Kristen

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-17 : 05:08:48

You could also use the bitwise AND operator

WHERE b.fld1 & b.fld2 & b.fld11 ... = 1

I don't like computed columns but you could do:


Alter Table tbx add fld19 cast (b.fld1 & b.fld2 & b.fld3 ... as bit)

fld19 would be a bit column that equaled 1 when all others were 1, 0 otherwise. You could make it varchar too if you wanted.

I don't like triggers that I can avoid either, but sometimes you have to pick the lesser of two evils. Do you want updates to be fast or queries?

You could also:


create view
v_mytable with schemabinding
as select ID, fld1, fld2, fld3,...  cast (b.fld1 & b.fld2 & b.fld3 ... as bit) fld19

Then index it


create unique clustered index v_flds_id_idx on v_mytable (Id)
then you could create an index on fld19
create index v_flds_19_idx on v_mytable (fld19)

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources