I am extremely new at SQL Server2000 and t-sql and I'm looking to
create a simple trigger. For explanation sake, let's say I have 3
columns in one table ... Col_1, Col_2 and Col_3. The data type for
Col_1 and Col_2 are bit and Col_3 is char. I want to set a trigger on
Col_2 to compare Col_1 to Col_2 when Col_2 is updated and if they're
the same, set the value on Col_3 to "Completed". Can someone please
help me?

Thanks,
Justin

Hi

Posting DDL (Create table statements etc.) and example data (as Insert
statements) removes any ambiguity. Within the trigger try something like:

UPDATE t
SET Col_3 = "Completed"
FROM MyTable t JOIN INSERTED i on i.pkcol = t.pkcol
JOIN DELETED d on d.pkcol = t.pkcol
WHERE i.col2 <> d.col2
AND i.col2 = i.col1

John

"Justin" <jhosman@numc.edu> wrote in message
news:450900ad.0407151958.73494eeb@posting.google.c om...
> I am extremely new at SQL Server2000 and t-sql and I'm looking to
> create a simple trigger. For explanation sake, let's say I have 3
> columns in one table ... Col_1, Col_2 and Col_3. The data type for
> Col_1 and Col_2 are bit and Col_3 is char. I want to set a trigger on
> Col_2 to compare Col_1 to Col_2 when Col_2 is updated and if they're
> the same, set the value on Col_3 to "Completed". Can someone please
> help me?
>
> Thanks,
> Justin

If col_3 is always based on the value of columns 1 and 2 then you don't need
it and you don't need the trigger either. Drop col_3 and calculate the
"completed" status in a view or when you query the table. Columns based on
other (non-key) columns are called Transitive Dependencies and avoiding them
is one of the goals of correct database design.

--
David Portas
SQL Server MVP
--

David,

You're right, of course but I was just giving an example. What I
actually have is 19 columns. 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.

Thanks,

Justin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi John,

Thanks for your help I really appreciate it. I tried your code and I
keep getting "invalid column name" error messages. Here is my attempt
at the code...

CREATE TRIGGER CHECKSTATUS
ON dbo.useraccounttbl
FOR UPDATE AS
IF UPDATE (internetcreated)
BEGIN
UPDATE t
SET completedstatus = "Completed"
FROM useraccounttbl t join INSERTED i on i.pkcol = t.pkcol
JOIN DELETED d on d.pkcol = t.pkcol
WHERE i.internetcreated <> d.internetccreated
AND i.internetcreated = i.internetrequested
END

I'm tring to test this on these 3 columns what I will need to do on 16
more. I explained it to another person who reponded like this...

What I actually have is 19 columns. 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
created 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.

Thanks again for all of your help.

Justin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi

pkcol are the columns in the primary key, it could be more than one column
that uniquely defines each row. As there was/is no DDL it was used as short
hand.

You can pair each value

UPDATE t
SET completedstatus = "Completed"
FROM useraccounttbl t join INSERTED i on i.pkcol = t.pkcol
JOIN DELETED d on d.pkcol = t.pkcol
WHERE ( i.col1 <> d.col1
AND i.col1 = i.col2 )
OR ( i.col3 <> d.col3
AND i.col3 = i.col4 )
OR ( .........
)

John

"Justin Hosman" <hateface72@yahoo.com> wrote in message
news:40f7dbfb$1$16414$c397aba@news.newsgroups.ws.. .
> Hi John,
>
> Thanks for your help I really appreciate it. I tried your code and I
> keep getting "invalid column name" error messages. Here is my attempt
> at the code...
>
> CREATE TRIGGER CHECKSTATUS
> ON dbo.useraccounttbl
> FOR UPDATE AS
> IF UPDATE (internetcreated)
> BEGIN
> UPDATE t
> SET completedstatus = "Completed"
> FROM useraccounttbl t join INSERTED i on i.pkcol = t.pkcol
> JOIN DELETED d on d.pkcol = t.pkcol
> WHERE i.internetcreated <> d.internetccreated
> AND i.internetcreated = i.internetrequested
> END
>
> I'm tring to test this on these 3 columns what I will need to do on 16
> more. I explained it to another person who reponded like this...
>
> What I actually have is 19 columns. 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
> created 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.
>
> Thanks again for all of your help.
>
> Justin
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

>> Here is my attempt at the code... <<

I am sure that someone will kludge something really ugly and complex
for you, but the real problem is that you are still an assembly level
programmer trying to move to an abstract high level language.

1) Stop putting that silly "-tbl" prefix on table names! This is
saying that your table models pieces of furniture that belong to user
accounts, according to ISO-11179. There is only one data structure in
SQL anyway. What you want is a name that tells us what set of
entities or relationship the table models; since it is a set,
something like "UserAccounts" is fine.

Volumns are not anything like Fields; until you know and think in
terms of rows and columns, you will never "get it" and will continue
to write BASIC in SQL.

2) SQL is a declarative language and probably the first such animal
you have ever seen. You tell it WHAT you want and it figures out HOW
to get it. This is a huge advance in programming. If the data
changes, the query stays the same but the execution plan changes with
you coding anything new.

>> What I actually have is 19 columns. 18 of them are bit value 1 of
them
char. The 18 represent 9 pairs. Each pair represents requested and
created. <<

ARRGH! Assembly language and punch card programming!! Think
relational, not physical. At best from this description, you have
nine attributes which ought to be in one column each; having "half an
atribute" is a design flaw called attribute splitting. At this point,
I have to guess at specs, but it looks like each attribute has two
values (requested and created). NEVER, NEVER, NEVER use BIT data (I
have an article due out soon in DBAzine with some of the many
reasons).

Sit down and design a status code with all the values you need; you
have
"requested" and "created", so nothing is needed for "failed" or
"rejected" in your situation?

The right way to do this is NOT with a trigger (procedural code!! The
HOW of the answer), but with a VIEW that will compute the status
(declarative code!! The WHAT of the answer).

>> 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
created by the various administrators. <<

Now we have specs and can see that you don't know about First Normal
Form (1NF). The nine pairs are a repeating group in the data model.
Let us "flatten" out the table.

CREATE TABLE UserAccounts
(user_id INTEGER NOT NULL -- tables relate to each other, files don't
REFERENCES Personnel (user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
admin_area INTEGER NOT NULL -- tables relate to each other, files
don't
REFERENCES Administration (admin_area)
ON DELETE CASCADE
ON UPDATE CASCADE,
request_status INTEGER DEFAULT 0 NOT NULL -- zero is "requested,not
granted"
CHECK (request_status IN (..)) -- columns have
constraints,fields don't
PRIMARY KEY (user_id, admin_area));

Your report:

CREATE VIEW RequestStatus (user_id, total_status)
AS
SELECT user_id, MIN(request_status)
FROM UserAccounts
GROUP BY user_id;

Add or drop admin areas as you wish; add more status codes easily; DRI
actions maintain the schema for you. The schema is a WHOLE, not a
collection of files that stand alone -- design the whole!

Justin Hosman (hateface72@yahoo.com) writes:
> Thanks for your help I really appreciate it. I tried your code and I
> keep getting "invalid column name" error messages. Here is my attempt
> at the code...
>
> CREATE TRIGGER CHECKSTATUS
> ON dbo.useraccounttbl
> FOR UPDATE AS
> IF UPDATE (internetcreated)
> BEGIN
> UPDATE t
> SET completedstatus = "Completed"
> FROM useraccounttbl t join INSERTED i on i.pkcol = t.pkcol
> JOIN DELETED d on d.pkcol = t.pkcol
> WHERE i.internetcreated <> d.internetccreated
> AND i.internetcreated = i.internetrequested
> END

"Completed" should be 'Completed'. SQL Server actually permits you
to use both " and ' to delimit strings, but " is only possible under
certain conditions, so stick with '.

> What I actually have is 19 columns. 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.

A computed column would be easier:

CREATE TABLE blablabla
(pkcol ....
request1 bit NOT NULL,
completed1 bit NOT NULL,
..
status AS (CASE WHEN request1 = completed1 AND
request2 = completed2 AND
...
request9 = comepleted9 THEN 'Completed'
ELSE 'Pending'
END)

However, this is not a very good design. You should probaly change
all these columns to rows, so that if a tenth request is added, all
you need to add one more row to defining table:


CREATE TABLE requesttypes
(reqtype char(3) NOT NULL, -- mnemonic
reqname varchar(40) NOT NULL, -- descriptive
CONSTRIAINT PK_reqtypes PRIMARY KEY requesttypes (reqtype))

CREATE TABLE requests (
usrid int NOT NULL,
reqtype char(3) NOT NULL,
status char(1) NOT NULL
-- New or completed
CONSTRAINT ckc_req_status CHECK (status IN ('N', 'C')),
CONSTRAINT pk_reqs PRIMARY KEY (usrid, reqtype),
CONSTRAINT fk_regtype FOREIGN KEY (reqtype)
REFERENCES requesttypes( requtype),
CONSTRAINR fk_userd FOREIGN KEY (usrid)
REFERENCES users (usrid))



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp