Create a trigger to update a row that's been inserted or updated

Hi

Apologies if this is a silly question

I have a basic table "Customer" which has

Id

Address1

Address2

Address3

Town

County

Postcode

SearchData

After I insert or Update a row in this table I need to update the SearchData column

with

UPPER(ADDRESS1) + UPPER(ADDRESS2) + UPPER(TOWN) + UPPER(POSTCODE)

only for that Id

I'm sure this is only a basic update but all the examples I can find are for inserting into other tables not the row itself.

Regards

David

Instead of using the trigger better you can go with Computed Columns...

here it is,

Code Snippet

Createtable Customer

(

IdintNOTNULL,

Address1varchar(100)NOTNULL,

Address2varchar(100)NOTNULL,

Address3varchar(100)NULL,

Townvarchar(100)NOTNULL,

Countyvarchar(100)NULL,

Postcodevarchar(100)NOTNULL,

SearchDataasUPPER(ADDRESS1)+UPPER(ADDRESS2)+UPPER(TOWN)+UPPER(POSTCODE)PERSISTED --Persisted only used on SQL Server 2005

)

|||I would not recommend using the 'PERSISTED' keyword unless you are attempting to solve a particular problem.|||

Hi

That was really helpful.

Thanks

David

Counting records

Hello,

I want to count some records in a table I have. My table is updated when someone posts information to an .asp page. Then, I use SSRS to create reports on this information in the table. I want to filter the results that the report shows based on how many records are in the table.

For instance:

Name last4 ID

John 2112 54432

John 3222 21223

John 7777 88888

John 3333 22222

John 3212 88722

Carol 2122 12111

Carol 5555 12111

Carol 3342 83635

Carol 1211 98363

Steve 2122 21331

James 2113 21123

Teresa 3223 21154

I want to filter the results shown in the report to records with > 3 occurances. So, the results for the above table would show only Carol and John.

Is there any way I can do this either in the SQL statement or in the reporting services statement?

Any help is appreciated.

Thanks

I think this ought to meet your needs

SELECT Name, last4, ID

FROM Some_Table

WHERE Name IN (

SELECT Name

FROM Some_Table

GROUP BY Name

HAVING COUNT(*) > 3

)

Just replace Some_Table with your table name.|||

Great. Thanks

I had something similar, but I had a few parts backwards. : )

So, if I wanted to add some more parameters should those go after the HAVING?

Like: HAVING COUNT (*) >3 AND Date BETWEEN @.startdate AND @.enddate

Thanks again.

|||Typically you would put the predicates that doesn't have any aggregate functions or GROUPING function in the WHERE clause. Logically the HAVING clause is seen as being evaluated after the GROUP BY clause has been evaluated so that the aggregate function results can be compared. This is different from the WHERE clause which can be evaluated at different stages (join conditions, table / index scans / seeks etc). So put the Date predicate in the WHERE clause and the COUNT(*) has to be in the HAVING clause.|||

So, if I understand correctly, it should be something like this:

SELECT Name, last4, ID

FROM Some_Table

WHERE Name IN (

SELECT Name

FROM Some_Table

GROUP BY Name

HAVING COUNT(*) > 3

) AND Date BETWEEN @.begindate AND @.enddate

Would that be the correct placement to include the date range in the WHERE clause?

Thanks again.

Counting query

Hi,
I have two tables: table 1 contains customer information, and table 2
contains order information.
Table 2 is updated everytime a customer orders some goods. Therefore, a
customer, for example, can appear within table 2 on, say, a total of 5
occasions.
I would like a column in table 1 that tells me how many orders the
corresponding customer has placed in total. Is there anyway of linking
table 1 with table 2 to count the total number of orders a particular
customer has made? (i.e. in the above case 5)
Thanks for your time
Paul Evans
While you can do this it is usually not a good idea. The main reason is
that now you have extra work somewhere (most likely a trigger) to keep that
value up to date and in some cases it can get out of sync. It is usually
better to simply issue a SUM or COUNT against the orders table with a WHERE
clause that filters by customer id. You would normally have an index on the
customer id and the operation would be pretty simple. If you use this value
a lot and there are not a large amount of new rows added to the Orders table
you might consider using an indexed view that sums up by customer. See more
in BOL on Indexed Views.
Andrew J. Kelly SQL MVP
"Paul Evans" <paul_evans1@.btinternet.com> wrote in message
news:uuR3pWS5EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two tables: table 1 contains customer information, and table 2
> contains order information.
> Table 2 is updated everytime a customer orders some goods. Therefore, a
> customer, for example, can appear within table 2 on, say, a total of 5
> occasions.
> I would like a column in table 1 that tells me how many orders the
> corresponding customer has placed in total. Is there anyway of linking
> table 1 with table 2 to count the total number of orders a particular
> customer has made? (i.e. in the above case 5)
> Thanks for your time
> Paul Evans
>