Help with CREATE TRIGGER syntax

Any help would be appreciated. What's wrong with the following syntax?
CREATE TRIGGER tr_CMR_Client_Status_Confirm
ON [CMR Client Numbers]
FOR INSERT, UPDATE
AS
IF UPDATE [Current Status]
CASE WHEN [Current Status] = 'X' THEN [Status Flag] = [CMR
Client Number ID] ELSE [Status Flag] = NULL END1. IF UPDATE should have parentheses around the column name - note: if
any row's [Current Status] is updated, this will evaluate to true.
2. not a syntax error, but for clarity, should put a BEGIN..END after the IF
3. [biggest problem] There's no DML statement to do anything, just a
CASE expression...
4. ... which is incorrectly written
Look up UPDATE, CREATE TRIGGER and CASE in BOL
It is quite unclear from the code given what table the [Status Flag]
column belongs to, otherwise I could give an example of possible correct
trigger code.
Please provide DDL of the table.
mike wrote:

>Any help would be appreciated. What's wrong with the following syntax?
>
>CREATE TRIGGER tr_CMR_Client_Status_Confirm
>ON [CMR Client Numbers]
>FOR INSERT, UPDATE
> AS
> IF UPDATE [Current Status]
> CASE WHEN [Current Status] = 'X' THEN [Status Flag] = [CMR
>Client Number ID] ELSE [Status Flag] = NULL END
>
>
>
>|||What are you trying to do? It looks like you want to duplicate a value in
another column of your table, which isn't a good thing to do.
Please post DDL and sample data.
David Portas
SQL Server MVP
--sql

Help with a query

Guys-

Any help with this query is greatly appreciated...

DECLARE @.input TABLE
(NodeId VARCHAR(10),
IsChecked CHAR(1))

DECLARE @.Actual TABLE
(AndSetId INT,
NodeId VARCHAR(10),
IsChecked CHAR(1))

INSERT INTO @.Input VALUES ('a', 'T')
INSERT INTO @.Input VALUES ('b', 'T')
INSERT INTO @.Input VALUES ('c', 'F')
INSERT INTO @.Input VALUES ('d', 'F')
INSERT INTO @.Input VALUES ('e', 'T')

INSERT INTO @.Actual VALUES (1, 'a', 'T')
INSERT INTO @.Actual VALUES (1, 'b', 'T')
INSERT INTO @.Actual VALUES (1, 'c', 'F')

INSERT INTO @.Actual VALUES (2, 'c', 'F')
INSERT INTO @.Actual VALUES (2, 'd', 'F')

INSERT INTO @.Actual VALUES (3, 'd', 'F')

INSERT INTO @.Actual VALUES (5, 'd', 'F')
INSERT INTO @.Actual VALUES (5, 'e', 'F')

INSERT INTO @.Actual VALUES (6, 'f', 'F')

INSERT INTO @.Actual VALUES (7, 'g', 'F')

-- We should get back 1, 2, 3, 4. We should not get back 5, 6, 7Did your instructor include the rules (the logic) behind this, or only give you the two inputs and the output that they expected? Have you recently covered anything like this in class?

-PatP|||Pat-

My apologies for not elaborating enough, what I was looking for.

Get all AndSetIds from @.Actual where

if NodeId is present in @.input then the isChecked should match.
eg: AndSetId 1
It has 3 records. And the records match with the records
in Input.
So 1 should be returned.

Where as let us look at AndSet 5.
It has NodeId 'e' as 'F' where as the input has 'e' as 'T'
So 5 should not be returned.

INSERT INTO @.Input VALUES ('a', 'T')
INSERT INTO @.Input VALUES ('b', 'T')
INSERT INTO @.Input VALUES ('c', 'F')
INSERT INTO @.Input VALUES ('d', 'F')
INSERT INTO @.Input VALUES ('e', 'T')

INSERT INTO @.Actual VALUES (1, 'a', 'T')
INSERT INTO @.Actual VALUES (1, 'b', 'T')
INSERT INTO @.Actual VALUES (1, 'c', 'F')

INSERT INTO @.Actual VALUES (2, 'c', 'F')
INSERT INTO @.Actual VALUES (2, 'd', 'F')

INSERT INTO @.Actual VALUES (3, 'd', 'F')

INSERT INTO @.Actual VALUES (5, 'd', 'F')
INSERT INTO @.Actual VALUES (5, 'e', 'F')

INSERT INTO @.Actual VALUES (6, 'f', 'F')

INSERT INTO @.Actual VALUES (7, 'g', 'F')|||Never mind. I think I have a solution. Thanks for your time Pat.sql

Help w/a SQL Query

Ok here's the deal. I'm not very good w/SQL so if I can get assistance with the exact commands it'd be greatly appreciated.

I want to add some conditions to copying items from one column into another (all in the same table)

I'll go head and give the field names too.

tblProduct is the table name
PartNumber and SearchTerm are the columns I'll be dealing with

I want to take everything that's in PartNumber and copy it into SearchTerm, but here are the conditions i need to set
Ignore Hyphens, so if I copy "a-b" it should come out as "ab"
&
All spaces should be converted to commas, so "a b" should be "a, b"
& i want to keep what's already in SearchTerm as is

I know it might be a lot to ask for but any help would be greatly appreciated.
Thank you.you would need to write a cursor or a set based query..something like :

DECLARE @.pnum varchar(50), @.sterm varchar(50), @.newpnum varchar(100)
DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT [id],PartNumber, SearchTerm FROM tblProduct
OPEN rs
fetch next from rs into @.id,@.pnum, @.sterm
WHILE ( @.@.FETCH_STATUS = 0 )
begin
SET @.newpnum = @.pnum
SET @.newpnum = REPLACE (@.newpnum, '-','') -- Ignoring the hyphons
SET @.newpnum = REPLACE (@.newpnum, ' ',',') -- Replacing spaces with comma's
SET @.newpnum = ISNULL(@.sterm,'') + @.newpnum
UPDATE tblProduct SET SearchTerm = @.newpnum WHERE CURRENT OF rs
FETCH NEXT FROM rs INTO @.id,@.pnum, @.sterm
END
CLOSE rs
DEALLOCATE rs

usually cursors are a performance hit but for one time operations like these I think its ok..you can use set based operations.with the same logic .its up to you

hth|||Thank you. I won't have a chance to try it out today but tomorrow I'll post back with my results.|||Worked like a dream. Thanks. :}