Newbie question on SQL Query writing

Hi,

I am new to writing SQL queries in MS SQL & would like to do the
following: Write a query to retrieve all strings that start with a
particular value.

Basically, I am looking for the SQL equivalent of the regex "^".

Thanks,
AshokSELECT column1 FROM mytable WHERE charindex('Search Text', column1) = 1|||On 18 May 2005 08:01:46 -0700, ashok.anbalan@.gmail.com wrote:

>Hi,
>I am new to writing SQL queries in MS SQL & would like to do the
>following: Write a query to retrieve all strings that start with a
>particular value.
>Basically, I am looking for the SQL equivalent of the regex "^".
>Thanks,
>Ashok

Hi Ashok,

Assuming they need to start with 'a':

SELECT Column list
FROM MyTable
WHERE TheStringColumn LIKE 'a%'

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

newbie question - sp help

hi all,

i need some helpful advise from you.
i need to write a sp that you may be able to help me with.

i have a table that only contains a numeric field. i need to write a
sp that, when executed, the numberic field increments by one and then
returns the new numeric. i have looked on the net for this, but have
not had any look...

can you please help in any way? i know nothing about writing sp's!

- hollis..Try something like:
/***********START SP

CREATE PROCEDURE numberTest @.numberIn int

AS

declare @.numberOut int

set @.numberOut = @.numberIn + 1

print @.numberOut

/***********END SP

You will need to manupulate to fit in excatly with what you need

JV
__________________________________________________ _________________
Remotely manage MS SQL db with SQLdirector -
www.ciquery.com/tools/sqldirector/

"Hollis" <hollis_uk@.lycos.co.uk> wrote in message
news:98c2c468.0309091201.50cd813d@.posting.google.c om...
> hi all,
> i need some helpful advise from you.
> i need to write a sp that you may be able to help me with.
> i have a table that only contains a numeric field. i need to write a
> sp that, when executed, the numberic field increments by one and then
> returns the new numeric. i have looked on the net for this, but have
> not had any look...
> can you please help in any way? i know nothing about writing sp's!
> - hollis..|||hollis_uk@.lycos.co.uk (Hollis) wrote in message news:<98c2c468.0309091201.50cd813d@.posting.google.com>...
> hi all,
> i need some helpful advise from you.
> i need to write a sp that you may be able to help me with.
> i have a table that only contains a numeric field. i need to write a
> sp that, when executed, the numberic field increments by one and then
> returns the new numeric. i have looked on the net for this, but have
> not had any look...
> can you please help in any way? i know nothing about writing sp's!
> - hollis..

This is one possible way:

create proc dbo.GetNextID
@.NextID int output
as
update dbo.MyTable
set @.NextID = NumericColumn = NumericColumn + 1
go

declare @.id int
exec dbo.GetNextID @.NextID = @.id output
select @.id
go

Simon

Newbie Q regarding opening a connection to a database

Hello, Im new with databases.

I know that opening a connection to a DB is expensive. Usually I write a method that opens a connection to the DB then I execute a query and then close the connection. Pretty standard.

OK, so how do I handle opening connections to the database when I need to run multiple queries. For example, i have a webpage that need to query the database to see if the user has moderator privledges, then depending on that query I have to query the DB again for moderator specific information or non-modertaor information.

So in this case how do i handle opening connections to the DB. Is it ok to generally have to open a connection to a DB multiple times on a page load?

The obvious solution is to keep the connection open. That is, open a connection, query the Db, keep the connection open, do the conditional statment ( if is_Moderator) then query the DB again for the info that I need, and then close the connection. But, from all the books that Ive been reading this is not a good practice because business logic should not been in the dataAccess layer.

Any help would be much appreciated.You should be using stored procedures to do all of this. Then this wouldn't be a problem. You could have a "wrapper" stored procedure that figures out if it's a moderator or not, then calls the appropriate stored procedure based on that information. You then wouldn't have to worry about multiple calls to the database. This is a data logic procedure. It's not "always" bad to have business logic at the database layer. It's questionable that this is even business logic.

Make sense?|||yes it did make sense.

Thankyou.

newbie pl/sql outputs select results

Hi,
I want to write a PL/SQL search engine that does some complex checking of various tables for a client's website. I could write it all in a PHP $sql = "SELECT ..." but I want to put it the sql into PL/SQL and am having trouble figuring out how PL/SQL outputs results.

Basically, I want to call the procedure with some keywords and have it return the results. Something like this:

create package jonsearch is

procedure getrecords(kw IN varchar, results OUT ?) is
begin
--complex sql goes here
end;

end jonsearch;

jonsearch.getrecords("keywords") would return the results just like select * from table would return results.

My trouble is that every tutorial I have read relies on dbms_output.put_line to output data. I want to output the results as a set, with an output variable, but I can't find a tutorial that shows how to use output variables.

Any help, even pointing me to a tutorial, would be great.

thanks,

JonIt turns out what I was looking for is called a REF CURSOR. I needed to create a package that defines this reference cursor, and then use that as my output variable.

This is described here, if anyone is interested:

http://www.oracle-base.com/Articles/8i/UsingRefCursorsToReturnRecordsets.asp|||Not understanding...

How are you wanting to output the variables? If you want to simply return the values, you will use the DBMS_OUTPUT package. If you are returning to Apache through the modplsql module, you will use the HTP and HTF packages.

As to REF Cursors, here is a Reader's Digest version:

http://www.dbforums.com/t974133.html

JoeB

Newbie needing help creating query

Hi,

I know some SQL but not enough to write the query i'm trying to create and
could do with some help!

I have 2 tables (Product and ProductProgram) that are linked by a common
identified 'ProductID'. Each product has 5 different price levels
(1,2,3,4,5) and these are stored in the ProductProgram table.

The ProductProgram table contains the following columns:

ProductID
Level
Price

The Product table has the following columns:

ProductID
Name
Description

Can anyone show me how to return each product with all 5 of their individual
price levels?SELECT a.ProductID,
a.Name,
a.Description,
p1.Price AS Price1,
p2.Price AS Price2,
p3.Price AS Price3,
p4.Price AS Price4,
p5.Price AS Price5
FROM Product a
LEFT OUTER JOIN ProductProgram p1 ON p1.ProductID=a.ProductID AND
p1.Level=1
LEFT OUTER JOIN ProductProgram p2 ON p2.ProductID=a.ProductID AND
p2.Level=2
LEFT OUTER JOIN ProductProgram p3 ON p3.ProductID=a.ProductID AND
p3.Level=3
LEFT OUTER JOIN ProductProgram p4 ON p4.ProductID=a.ProductID AND
p4.Level=4
LEFT OUTER JOIN ProductProgram p5 ON p5.ProductID=a.ProductID AND
p5.Level=5|||Hi Mark,

Thanks very much! I would never have gotten there on my own!

Mintyman

<markc600@.hotmail.comwrote in message
news:1163429919.872807.120980@.b28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

SELECT a.ProductID,
a.Name,
a.Description,
p1.Price AS Price1,
p2.Price AS Price2,
p3.Price AS Price3,
p4.Price AS Price4,
p5.Price AS Price5
FROM Product a
LEFT OUTER JOIN ProductProgram p1 ON p1.ProductID=a.ProductID AND
p1.Level=1
LEFT OUTER JOIN ProductProgram p2 ON p2.ProductID=a.ProductID AND
p2.Level=2
LEFT OUTER JOIN ProductProgram p3 ON p3.ProductID=a.ProductID AND
p3.Level=3
LEFT OUTER JOIN ProductProgram p4 ON p4.ProductID=a.ProductID AND
p4.Level=4
LEFT OUTER JOIN ProductProgram p5 ON p5.ProductID=a.ProductID AND
p5.Level=5
>

Newbie need help on trigger.

Hello,
I need to write a trigger that removes repeated elements on a table.
I want to delete every row where the field "name" has beenrepeated.
Any hel in writing such a trigger would be so much higly appreciated.
Many thinks in advance
JBWhy do this via a trigger? Wouldn't it be easier to not insert duplicate
rows to begin with?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jensen bredal" <jensen.bredahl@.yahoo.com> wrote in message
news:O9xhu5PHFHA.3624@.tk2msftngp13.phx.gbl...
> Hello,
> I need to write a trigger that removes repeated elements on a table.
> I want to delete every row where the field "name" has beenrepeated.
> Any hel in writing such a trigger would be so much higly appreciated.
> Many thinks in advance
> JB
>|||While this is a very good question, it appears that we
need the other option.
We are doing something very unusual. This is a system
integration project and we are sharing this database with other systems.
I won''t go in furthere details and hope that make sens.
Many thanks
JB|||You could try an INSTEAD OF trigger:
CREATE TRIGGER TG_NoDupes
ON YourTable
FOR INSERT
AS
BEGIN
IF @.@.ROWCOUNT = 0
RETURN
INSERT YourTable
SELECT *
FROM INSERTED
WHERE NOT EXISTS
(SELECT *
FROM YourTable
WHERE YourTable.Name = INSERTED.Name)
END
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jensen bredal" <jensen.bredahl@.yahoo.com> wrote in message
news:%23SifbHQHFHA.1176@.TK2MSFTNGP12.phx.gbl...
> While this is a very good question, it appears that we
> need the other option.
> We are doing something very unusual. This is a system
> integration project and we are sharing this database with other systems.
> I won''t go in furthere details and hope that make sens.
> Many thanks
> JB
>|||Jensen,
Unless I'm really not understanding, what you are asking for is inherently
inconsistent. Either you want a PROCEDURE that will delete all instances
where name has been repeated, or you want a TRIGGER to PREVENT the insert of
such records in the first place. An insert TRIGGER, for eg, will fire and
run EVERY TIME a record is inserted. If you wrote it to delete all existing
duplicates, it would be re-running that code over and over- again
unnecessarily, on every insert, when the first run would have already delete
d
all existing duplicates.
What it sounds like yuou might actually need, (excuse me if I've
mistunderastood) is a procedure to run ONCE to delete all existing
duplicates, and a trigger, (actually a unique constraint on the name Column
would do it) that would prevent furthur inserts of duplicates in the future.
Anyway, if so, for the first step, to eliminate existing dupes, sasuming the
table has a Primary Key, called say "PKID", try this:
Delete T
From TableName T
Where Name In (Select Name From TableName
Group By Name
Having Count(*) > 1)
And PKID <> (Select Min(PKID)
From TableName
Where Name = T.Name)
"Jensen bredal" wrote:

> Hello,
> I need to write a trigger that removes repeated elements on a table.
> I want to delete every row where the field "name" has beenrepeated.
> Any hel in writing such a trigger would be so much higly appreciated.
> Many thinks in advance
> JB
>
>|||"Jensen bredal" <jensen.bredahl@.yahoo.com> wrote in message
news:O9xhu5PHFHA.3624@.tk2msftngp13.phx.gbl...
> Hello,
> I need to write a trigger that removes repeated elements on a table.
> I want to delete every row where the field "name" has beenrepeated.
> Any hel in writing such a trigger would be so much higly
appreciated.
> Many thinks in advance
> JB
>
Jensen bredal,
May I ask why you are operating a table without a Primary Key? (Yes,
I did read the other portion of the thread where the "We're doing
something unusual" answer was given, but I'm still curious).
From the description, it sounds like you want to keep out duplicate
records. This is the effect a Primary Key has, and it's a lot faster
than any trigger.
Sincerely,
Chris O.|||Well as i said this is not the every day scenario.
I may give you a full explaination if you have time to read my answer. Let
me know if you want that.
JB
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:MPednYwUXbNt_r_fRVn-sA@.comcast.com...
> "Jensen bredal" <jensen.bredahl@.yahoo.com> wrote in message
> news:O9xhu5PHFHA.3624@.tk2msftngp13.phx.gbl...
> appreciated.
> Jensen bredal,
> May I ask why you are operating a table without a Primary Key? (Yes,
> I did read the other portion of the thread where the "We're doing
> something unusual" answer was given, but I'm still curious).
> From the description, it sounds like you want to keep out duplicate
> records. This is the effect a Primary Key has, and it's a lot faster
> than any trigger.
>
> Sincerely,
> Chris O.
>

newbie need help

I need to know how to write a sql statement for VB to Microsoft Access for the following criteria.

TblEmployees only Field to be concerned with is EmployeeID -Its PK
TblEmpAttendane has these fields:
EntryID - Pk
EmployeeID - Fk
Date- A text form date validated through vb to avoid hassle
Value-A single digit value that can be alpha OR numeric

To get the Date range for the current month I use variables to store the First and last day of the month(in a CUSTOM calendar control).

I have been trying to use this Sql statement(that wont work):
SqlString = "SELECT * FROM TblEmpAttendance WHERE Date BETWEEN '" & FirstDay & "' AND '" & LastDay & "'"

I need to:
1.)using the current EmployeeID (from TblEmployees)
2.) find the same EmployeeID in TblEmpAttendance
3.)Get the date range for the current month and year(valid days currnt mo.)
-these date are supplied by the variables "FirstDay" AND "LastDay" as seen in above SqlString

4.) Find the Values associated with the dates and EmployeeIDAre you using Jet or MS-SQL (aka MSDE) as your database engine? They have differences in how they handle dates, and the example you gave would not fly very well in Jet.

-PatP|||Im using jet to connect(through code) to my db.
Can u assist me in how to improve my table format..or whatever I would have to do in order to be able to do this correctly?

All that I did was set up a string format in vb to make sure that the correct amount of characters and the proper syntax was used:
eg... 00/00/0000
In my code I nvr explicity refer to them as a date. Only the user would think that it was a date!

Thanks-Greg S|||I'd try using:SqlString = "SELECT * FROM TblEmpAttendance WHERE #" _
& FirstDay & "# <= Date AND Date <= #" & LastDay & "#"This is only a swag, but I think that it should work.

-PatP|||Thanks I will try that and get back to you. Much appreciated!|||I have a lot more to do than I thought. I did do a date conversion function from within access so it is only displayed as dd/mm/yy format.
Then from vb I used:
SqlString = "SELECT * FROM TblEmpAttendance WHERE Date Bewteen '" & FirstDay & "' AND '" & LastDay & "'"

It seemd to work fine as I can refer to a field but for some reason when I refer to the recordcount property,I always get -1...Seems weird how I can read the info but not get the recordcount!!|||Check the BOL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprorecordcount.asp). If you use ADOpenStatic you won't have -1 as a recordcount.

-PatP|||I dont know what you mean by "BOL" but as I am using ADO as a connection it does support bookmarks(not that I believe I need on with the move.bof statement) If this is not what you were getting at could you be more specific?

OK WAIT..I added adOpenStatic to my statement!
now it seems to be working
But I had to put in in the form of:
AdoRecordset.Open SqlString, AdoConnection, adOpenStatic

instead of AdoRecordset.Open (SqlString, AdoConnection), adOpenStatic
like the link you posted...Im still not sure why the perverbial correct way wouldnt work but my way worked anyhow..
THANKS MUCH!|||adOpenStatic, adOpenDynamic, adOpenKeyset, and adOpenForwardOnly are qualifications of the cursor that is being open either on the server or client side (depends on the CursorLocation property). The default (adOpenForwardOnly) will have -1 for RecordCount property.

newbie lookup table

How would I run a query, on the server side, on a table that would pull from
a second table (like a lookup table) and write the result to each row in a
record set of the first table?
ThanksHi,
UPDATE TableOne T1
SET T1.ColumnToUpdate = T2.ColumnToRetrieveValueFrom
FROM Table1 as T1
INNER JOIN Table2 as T2
ON (T1.RelatedKey = T2.RelatedKey)
Allright ?
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"HollyylloH" <HollyylloH@.discussions.microsoft.com> schrieb im Newsbeitrag
news:64F2B05C-A982-4709-9DB2-2F5B9431EF88@.microsoft.com...
> How would I run a query, on the server side, on a table that would pull
> from
> a second table (like a lookup table) and write the result to each row in a
> record set of the first table?
> Thanks|||UPDATE TableOne
SET T1.ColumnToUpdate
= (SELECT T2.ColumnToRetrieveValueFrom
FROM Table2 AS T2
WHERE TableOne.RelatedKey = T2.RelatedKey) ;

Newbie (to Triggers) Trigger Help....

Where can I find good information about how to write triggers? I'm using SQL
Server 2000.
Now, the task at hand: How can I write a trigger that reacts on both insert
and update to keep some fields in two similar (but not the same) tables in
sync. (I need to both insert and update the 2nd table).
Here's some sample DBs (is there a better way to describe the database?):
CREATE TABLE [dbo][tblUserMain](
[userID] [int] IDENTITY (1, 1) NOT NULL ,
[CN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[userPassword] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[dtLastActive] [datetime] NOT NULL ,
[GUID] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[firstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[billCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[billCountry] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[billName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[billPostal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[billProvince] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[billStreet1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[billStreet2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblUserExt] (
[CN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[firstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[userEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[userRefBy] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[userProfession] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[userBulkMail] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[userSubspecialties] [nvarchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[userTechnologies] [nvarchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[dtRegDate] [datetime] NOT NULL ,
[userPopQuiz] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[orgCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[orgProvince] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[orgPostal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[orgCountry] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[orgType] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[userAdvertise] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[userBulkMailHtml] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[userTitle] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[orgName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[orgStreet1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[orgStreet2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[userHomeTelephone] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[userWorkTelephone] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[billCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[billCountry] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[billName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[billPostal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[billProvince] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[billStreet1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[billStreet2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[userDegree] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[needsUpdate] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SavedSearches] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[userGroups] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[userPersonalize] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[userRememberMe] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[middleName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[needsUpdateMessage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[pwdQuestion] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pwdAnswer] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[homePage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[language] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mailerEmailInvalid] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I need a trigger so that when the lastName, firstName and bill* fields
change (or insert) in the tblUserExt table they get updated (or inserted)
into the tblUserMain table. These triggers would be temporary until the
tblUserExt table is made obsolete...
Pseudocode--
On Insert,
Update the name and bill* fields in the tblUserMain table (the row will
already exist)
On Update,
Update the name and bill* fields (if they changed) in the tblUserMain
table
TIA,
OwenCREATE TRIGGER Triger_name ON tblUserExt
FOR INSERT
AS
DECLARE @.name NVARCHAR(50)
DECLARE @.billname NVARCHAR(50)
SELECT @.name=name FROM INSERTED
SELECT @.billname=billname FROM INSERTED
INSERT INTO tblUserMain (name,billname) VALUES (@.name,@.billname)
-- or INSERT INTO tblUserMain (the required fields) SELECT
ins.the_same_required_ fileds FROM INSERTED ins
----
CREATE TRIGGER Triger_name ON tblUserExt
FOR UPDATE
AS
DECLARE @.name NVARCHAR(50)
DECLARE @.billname NVARCHAR(50)
DECLARE @.Oldname NVARCHAR(50) --before updated
DECLARE @.Oldbillname NVARCHAR(50)--before updated
SELECT @.name=name FROM INSERTED
SELECT @.billname=billname FROM INSERTED
SELECT @.Oldname=name FROM tblUserExt
SELECT @.Oldbillname=billname FROM tblUserExt
UPDATE tblUserMain
SET name = @.name,
billname = @.billname
WHERE name = @.name AND billname = @.Oldbillname
Note that @.name has to be changed to all the fields like
firstname,lastname,..., same thong for billname.
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> a crit dans le message de news:
elI550wVFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Where can I find good information about how to write triggers? I'm using
> SQL Server 2000.
> Now, the task at hand: How can I write a trigger that reacts on both
> insert and update to keep some fields in two similar (but not the same)
> tables in sync. (I need to both insert and update the 2nd table).
> Here's some sample DBs (is there a better way to describe the database?):
> CREATE TABLE [dbo][tblUserMain](
> [userID] [int] IDENTITY (1, 1) NOT NULL ,
> [CN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [userPassword] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [dtLastActive] [datetime] NOT NULL ,
> [GUID] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [firstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [lastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [billCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [billCountry] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [billName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [billPostal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [billProvince] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [billStreet1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [billStreet2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[tblUserExt] (
> [CN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [firstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [lastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [userEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [userRefBy] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [userProfession] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userBulkMail] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userSubspecialties] [nvarchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [userTechnologies] [nvarchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [dtRegDate] [datetime] NOT NULL ,
> [userPopQuiz] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [orgCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [orgProvince] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [orgPostal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [orgCountry] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [orgType] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [userAdvertise] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userBulkMailHtml] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userTitle] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [orgName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [orgStreet1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [orgStreet2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [userHomeTelephone] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [userWorkTelephone] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [billCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [billCountry] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [billName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [billPostal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [billProvince] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [billStreet1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [billStreet2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userDegree] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [needsUpdate] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SavedSearches] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userGroups] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [userPersonalize] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userRememberMe] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [middleName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [needsUpdateMessage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [pwdQuestion] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pwdAnswer] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [homePage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [language] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [mailerEmailInvalid] [int] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> I need a trigger so that when the lastName, firstName and bill* fields
> change (or insert) in the tblUserExt table they get updated (or inserted)
> into the tblUserMain table. These triggers would be temporary until the
> tblUserExt table is made obsolete...
> Pseudocode--
> On Insert,
> Update the name and bill* fields in the tblUserMain table (the row will
> already exist)
> On Update,
> Update the name and bill* fields (if they changed) in the tblUserMain
> table
> TIA,
> Owen
>|||Regarding: "Here's some sample DBs (is there a better way to describe
the database?)" - What you have provided is fine, but any constraints
(primary key, unique and foreign keys) are also needed. If you
generated this thru Query Analyzer, you can set the options for DDL
generation.
Use the Tools-->Options menu item and then the scripts tab.
Here is part of an update trigger to syncronize the First and Last Name.
This assumes that, in both tables, the column named CN is unique and has
the same value. I have included the comparison logic for First and Last
Name allowing nulls.
CREATE TRIGGER tblUserExt_tua -- Trigger Update After
ON tblUserExt FOR UPDATE
AS
set nocount on
set xact_abort on
-- Check if any rows affected by the command
declare @.Rows integer
SELECT @.Rows = count(*) from inserted
IF @.rows = 0 return
UPDATE dbo.tblUserMain
SET firstName = inserted.firstName
, lastName = inserted.lastName
FROM inserted
WHERE dbo.tblUserMain.CN = inserted.CN
AND ( inserted.firstName <> dbo.tblUserMain.firstName
OR ( inserted.firstName IS NULL
and dbo.tblUserMain.firstName IS NOT NULL
)
OR ( inserted.firstName IS NOT NULL
and dbo.tblUserMain.firstName IS NULL
)
OR inserted.lastName <> dbo.tblUserMain.lastName
OR ( inserted.lastName IS NOT NULL
and dbo.tblUserMain.firstName IS NULL
)
OR ( inserted.lastName IS NULL
and dbo.tblUserMain.firstName IS NOT NULL
)
)
)
go
*** Sent via Developersdex http://www.examnotes.net ***|||On Thu, 12 May 2005 18:20:05 +0100, Berimi wrote:

>CREATE TRIGGER Triger_name ON tblUserExt
>FOR INSERT
>AS
>DECLARE @.name NVARCHAR(50)
>DECLARE @.billname NVARCHAR(50)
>SELECT @.name=name FROM INSERTED
>SELECT @.billname=billname FROM INSERTED
(snip)
Hi Berimi,
This trigger (and the trigger you wrote for UPDATE) will fail as soon as
an insert or update statement is executed that affects more than one
row. And it will fail even worse when a statement is executed that
affects no rows.
Always write triggers that can handle multi-row and zero-row operations!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 12 May 2005 10:30:54 -0700, Carl Federl wrote:
(snip)
>CREATE TRIGGER tblUserExt_tua -- Trigger Update After
> ON tblUserExt FOR UPDATE
If I understand the OP's requirements correct, one trigger can handle
both inserts and updates:
ON tblUserExt FOR INSERT, UPDATE

>-- Check if any rows affected by the command
>declare @.Rows integer
>SELECT @.Rows = count(*) from inserted
>IF @.rows = 0 return
This will waste unnecessary time when 1000s of rows were affected. Use
EXISTS instead:
IF NOT EXISTS (SELECT * FROM inserted) RETURN
Or, better yet, use @.@.ROWCOUNT (at the start of a trigger, this holds
the number of rows affected by the stmt that fired the trigger):
IF @.@.ROWCOUNT = 0 RETURN

>UPDATE dbo.tblUserMain
>SET firstName = inserted.firstName
>, lastName = inserted.lastName
>FROM inserted
>WHERE dbo.tblUserMain.CN = inserted.CN
>AND ( inserted.firstName <> dbo.tblUserMain.firstName
> OR ( inserted.firstName IS NULL
> and dbo.tblUserMain.firstName IS NOT NULL
> )
> OR ( inserted.firstName IS NOT NULL
> and dbo.tblUserMain.firstName IS NULL
> )
> OR inserted.lastName <> dbo.tblUserMain.lastName
> OR ( inserted.lastName IS NOT NULL
> and dbo.tblUserMain.firstName IS NULL
> )
> OR ( inserted.lastName IS NULL
> and dbo.tblUserMain.firstName IS NOT NULL
> )
> )
> )
The test for changed data in nullable columns can be done in a shorter
form. It's less intuitive on first sight, but it saves you lots of lines
of code (important if this has to grow to accomodate 50-odd columns!),
and it's easy once you get used to it:
UPDATE u
SET firstName = i.firstName
, lastName = i.lastName
FROM dbo.tblUserMain AS u
INNER JOIN inserted AS i
ON i.CN = u.CN
WHERE ( NULLIF (i.firstName, u.firstName) IS NOT NULL
OR NULLIF (u.firstName, i.firstName) IS NOT NULL)
AND ( NULLIF (i.lastName, u.lastName) IS NOT NULL
OR NULLIF (u.lastName, i.lastName) IS NOT NULL)
Of course, the check for unchanged data could be left out completely.
Without it, finding the rows to operate on would be much quicker, at the
cost of possibly updating the values in some rows to the values they
already had. To prevent updating when no first or last names have been
changed, you can add (before the UPDATE statement):
IF UPDATE(FirstName) OR UPDATE(LastName)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||You're right Hugo,
Thanks,
T.Berimi
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> a crit dans le message de
news: l5l781lt68ctijp3ov37rjv7cd0m5l1ifu@.4ax.com...
> On Thu, 12 May 2005 18:20:05 +0100, Berimi wrote:
>
> (snip)
> Hi Berimi,
> This trigger (and the trigger you wrote for UPDATE) will fail as soon as
> an insert or update statement is executed that affects more than one
> row. And it will fail even worse when a statement is executed that
> affects no rows.
> Always write triggers that can handle multi-row and zero-row operations!
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Newbie - Student - Help

I am working on an assignment that was due at midnight. The question is usi
ng the "Northwind database write the following SQL statements to be executed
against on of the tables within the "Northwind database"
a.) Alter a table and add a column
b.) Alter a table and add a constraint
c.) Alter a table and add an index
What I have so far is:
USE Northwind
ALTER TABLE Employees ADD COLUMN TerminationDate DateTime NULL
Server: Msg 156, Level 15, State 1, Line 3
I keep getting "Incorrect syntax near the keyword 'COLUMN'"
What am I doing wrong? If I can not get part 'a' then I will never get the
rest of the assignment.
Thanks in advance,
Deanna
UoP Student
deannac24@.cableone.netI am using SQL Query Analyzer
--
Deanna
UoP Student
deannac24@.cableone.net
"Deanna Cusic" <deannac24@.cableone.net> wrote in message news:11lh29913e9ngd
5@.corp.supernews.com...
I am working on an assignment that was due at midnight. The question is usi
ng the "Northwind database write the following SQL statements to be executed
against on of the tables within the "Northwind database"
a.) Alter a table and add a column
b.) Alter a table and add a constraint
c.) Alter a table and add an index
What I have so far is:
USE Northwind
ALTER TABLE Employees ADD COLUMN TerminationDate DateTime NULL
Server: Msg 156, Level 15, State 1, Line 3
I keep getting "Incorrect syntax near the keyword 'COLUMN'"
What am I doing wrong? If I can not get part 'a' then I will never get the
rest of the assignment.
Thanks in advance,
Deanna
UoP Student
deannac24@.cableone.net|||Deanna Cusic skrev:

> What I have so far is:
> USE Northwind
> ALTER TABLE Employees ADD COLUMN TerminationDate DateTime NULL
>
> Server: Msg 156, Level 15, State 1, Line 3
> I keep getting "Incorrect syntax near the keyword 'COLUMN'"
>
Have you checked Books online, the help that comes with SQL Server?
Using that you should be able to work the syntax out! Or check eg.
http://msdn.microsoft.com/library/d...
server2000.asp
if you don't have access to BOL.
Other than that, try losing the 'COLUMN' part.
/impslayer, aka Birger Johansson|||"impslayer" <impslayer@.hotmail.com> wrote in message
news:1129876626.682799.308040@.f14g2000cwb.googlegroups.com...
> Deanna Cusic skrev:
>
> Have you checked Books online, the help that comes with SQL Server?
> Using that you should be able to work the syntax out! Or check eg.
> http://msdn.microsoft.com/library/d...lserver2000.asp
> if you don't have access to BOL.
> Other than that, try losing the 'COLUMN' part.
> /impslayer, aka Birger Johansson
>
Thank you, all I did is take out 'COLUMN' and it works. I have been
wracking my brain for hours over this.
Deanna
UoP Student
deannac24@.cableone.net|||OK, thanks for your help so far but now I am stuck on the final one. I am
trying to add an index. So far I have:
use Northwind
ALTER TABLE Employees ADD [idxAddress] nvarchar(60), Address nvarchar(60)
the error I get is
Server: Msg 2705, level 16, State 4, Line 3
Column names in each table must be unique. Column name 'Address' in table
'Employees' is specified more than once.
I have tried this on many different fields including the primary keys and
still get this same error. I have even pulled up the data to check for
replication and did not find any. I am lost. I finally, thanks to help,
have part a and b, however I need part c. When I get that I can actually
get some sleep before the sun, and my children, get up ;-)
--
Deanna
"Deanna Cusic" <deannac24@.cableone.net> wrote in message
news:11lh4epilg52q9b@.corp.supernews.com...
> "impslayer" <impslayer@.hotmail.com> wrote in message
> news:1129876626.682799.308040@.f14g2000cwb.googlegroups.com...
> Thank you, all I did is take out 'COLUMN' and it works. I have been
> wracking my brain for hours over this.
> Deanna
> UoP Student
> deannac24@.cableone.net
>|||Deanna Cusic skrev:

> OK, thanks for your help so far but now I am stuck on the final one. I am
> trying to add an index. So far I have:
> use Northwind
> ALTER TABLE Employees ADD [idxAddress] nvarchar(60), Address nvarchar(60)
>
You should look for help on creating an index, not 'ALTER TABLE'...
The assignment question seemed to indicate an 'ALTER TABLE', but
you should really search for help on 'index' instead, that would
give you your desired answer!
Without Books online, you might check out:
http://msdn.microsoft.com/library/d...r />
_64l4.asp
/impslayer, aka Birger Johansson