INSERT UPDATE Trigger Question

How would I write a trigger that updates the values of a Description
column to upper case for even IDs and to lower case for odd IDs? I
need this trigger to fire for INSERT and UPDATE events.Hi

Try something like:

CREATE TABLE Test ( id int not null identity (1,1), Description char(10) )

CREATE TRIGGER Test_Insert ON Test FOR INSERT AS
UPDATE TEST SET Description = CASE Id%2 WHEN 0 THEN UPPER(Description) ELSE
LOWER (Description) END

INSERT INTO TEST ( Description ) VALUES ('One')
INSERT INTO TEST ( Description ) VALUES ('Two')
INSERT INTO TEST ( Description ) VALUES ('Three')
INSERT INTO TEST ( Description ) VALUES ('four')
INSERT INTO TEST ( Description ) VALUES ('five')
INSERT INTO TEST ( Description ) VALUES ('SIX')
INSERT INTO TEST ( Description ) VALUES ('SEVEN')

SELECT * from Test

John

<imani_technology@.yahoo.com> wrote in message
news:f9208446.0309011615.5f269625@.posting.google.c om...
> How would I write a trigger that updates the values of a Description
> column to upper case for even IDs and to lower case for odd IDs? I
> need this trigger to fire for INSERT and UPDATE events.|||imani_technology@.yahoo.com wrote in message news:<f9208446.0309011615.5f269625@.posting.google.com>...
> How would I write a trigger that updates the values of a Description
> column to upper case for even IDs and to lower case for odd IDs? I
> need this trigger to fire for INSERT and UPDATE events.

You might want to consider formatting the text on the client, when you
retrieve it from the database - presentation tasks don't really belong
in a database. But if you want to do it using a trigger, something
like this should work (assuming your 'ID' is an integer key column):

create trigger ATR_UI_MyTable
on dbo.MyTable after insert, update
as
update dbo.MyTable
set DescriptionColumn =
case i.IDColumn % 2
when 1 then lower(i.DescriptionColumn)
when 0 then upper(i.DescriptionColumn)
end
from dbo.MyTable t
join inserted i
on t.IDColumn = i.IDColumn

Simon

insert statement help

hi,
i have a small question regarding sql, there are two tables that i
need to work with on this, one has fields like:
Table1:
(id, name, street, city, zip, phone, fax, etc...) about 20 more
columns
Table2:
name
what i need help with is that table2 contains about 200 distinct names
that i need to insert into table1, i'm using sql server, is there a
way to insert them into table1?? i'm not sure how to write a query
within the insert statment to get them inserted into table1?
something like:
insert into table(id, name, street, zip, phone, fax, ...)
values(newid(), (select distinct name from table2), null, null,
null...)
and is there a way to do it without all the nulls having to be put in,
there are about 20 more columns in table1, and id in table1 is unique.[posted and mailed, please reply in news]

soni29 (soni29@.hotmail.com) writes:
> i have a small question regarding sql, there are two tables that i
> need to work with on this, one has fields like:
> Table1:
> (id, name, street, city, zip, phone, fax, etc...) about 20 more
> columns
> Table2:
> name
> what i need help with is that table2 contains about 200 distinct names
> that i need to insert into table1, i'm using sql server, is there a
> way to insert them into table1?? i'm not sure how to write a query
> within the insert statment to get them inserted into table1?
> something like:
> insert into table(id, name, street, zip, phone, fax, ...)
> values(newid(), (select distinct name from table2), null, null,
> null...)
> and is there a way to do it without all the nulls having to be put in,
> there are about 20 more columns in table1, and id in table1 is unique.

Your question is a bit vague, and since I don't see the tables, nor do
I see the data, I have to guess.

If all you want to is to insert the disctinct names in table2 into table1,
without providing any values for the other columns, save the id column,
this is the statement:

INSERT table1 (id, name)
SELECT disctint newid(), name FROM table2

Thus, you do need to list a column in the column list of the INSERT
statement, if you wish to set it to NULL or its default value.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Check out the insert syntax in books online (use the Go/URL menus!):

mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\acd
ata.chm::/ac_8_md_03_1kz8.htm

If the columns are nullable and don't have a value or if the are not
nullable and take the default then you do not have to mention them in the
select statement. If they are nullable then the DEFAULT keyword can be used.

To create a default for your id column then it can be declare with a default
see:

mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_na-nop_4pt0.htm

i.e.

CREATE TABLE cust
(
id uniqueidentifier NOT NULL
DEFAULT newid(),
....

)
GO

Therefore you can do something like:

insert into table(name, street, zip, phone, fax)
select distinct name, street, zip, phone, fax from table2

If you can not get distinct from this then you may need a subquery such as:

mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_11_3smm.htm

John

"soni29" <soni29@.hotmail.com> wrote in message
news:cad7a075.0312140855.7c8b2475@.posting.google.c om...
> hi,
> i have a small question regarding sql, there are two tables that i
> need to work with on this, one has fields like:
> Table1:
> (id, name, street, city, zip, phone, fax, etc...) about 20 more
> columns
> Table2:
> name
> what i need help with is that table2 contains about 200 distinct names
> that i need to insert into table1, i'm using sql server, is there a
> way to insert them into table1?? i'm not sure how to write a query
> within the insert statment to get them inserted into table1?
> something like:
> insert into table(id, name, street, zip, phone, fax, ...)
> values(newid(), (select distinct name from table2), null, null,
> null...)
> and is there a way to do it without all the nulls having to be put in,
> there are about 20 more columns in table1, and id in table1 is unique.|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9451C9DBAF97CYazorman@.127.0.0.1...
> [posted and mailed, please reply in news]
> soni29 (soni29@.hotmail.com) writes:
> > i have a small question regarding sql, there are two tables that i
> > need to work with on this, one has fields like:
> > Table1:
> > (id, name, street, city, zip, phone, fax, etc...) about 20 more
> > columns
> > Table2:
> > name
> > what i need help with is that table2 contains about 200 distinct names
> > that i need to insert into table1, i'm using sql server, is there a
> > way to insert them into table1?? i'm not sure how to write a query
> > within the insert statment to get them inserted into table1?
> > something like:
> > insert into table(id, name, street, zip, phone, fax, ...)
> > values(newid(), (select distinct name from table2), null, null,
> > null...)
> > and is there a way to do it without all the nulls having to be put in,
> > there are about 20 more columns in table1, and id in table1 is unique.
> Your question is a bit vague, and since I don't see the tables, nor do
> I see the data, I have to guess.
> If all you want to is to insert the disctinct names in table2 into table1,
> without providing any values for the other columns, save the id column,
> this is the statement:
> INSERT table1 (id, name)
> SELECT disctint newid(), name FROM table2
> Thus, you do need to list a column in the column list of the INSERT
> statement, if you wish to set it to NULL or its default value.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

A minor correction - the syntax above will generate a new uniqueidentifier
for each row in the source table before applying the DISTINCT, so you will
get all the values from the source table anyway. Something like this should
work correctly:

insert into table1 (id, name)
select newid(), name from
(
select distinct name
from table2 ) dt

Although as you pointed out, without seeing data and DDL, it's not at all
clear what 'correctly' means here, so my version may not be what the poster
wants either.

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> A minor correction - the syntax above will generate a new uniqueidentifier
> for each row in the source table before applying the DISTINCT, so you will
> get all the values from the source table anyway.

Oops!

Thanks for the correction, Simon!

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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