[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

TRIGGER

CipherTeKST

3/22/2007 9:56:00 PM

I am trying to create a trigger that will update a second column once a first
column is updated in the same row. For example I have a table with several
columns, I need for the trigger to check column1 to see if the integer has
reached 100 if so then update column2 in the same record. What I have so far
is...
CREATE TRIGGER trigger1
ON table1
FOR UPDATE
AS
BEGIN
IF UPDATE(colum1)
SELECT
i.colum1
FROM inserted i
IF i.column1 = '100'
UPDATE table1
SET column2 = 'value'
END

I'm not sure if this is how the syntax is supposed to look, I keep getting a
message saying error in syntax near '100'. Please... any help will be GREATLY
APPRECIATED.
Thanks.
--
CipherTeKST
MCSE: Security 2003, CCNA, Security+
5 Answers

xyb

3/23/2007 12:43:00 AM

0

On 3?23?, ??5?56?, CipherTeKST <CipherTe...@discussions.microsoft.com>
wrote:
> I am trying to create a trigger that will update a second column once a first
> column is updated in the same row. For example I have a table with several
> columns, I need for the trigger to check column1 to see if the integer has
> reached 100 if so then update column2 in the same record. What I have so far
> is...
> CREATE TRIGGER trigger1
> ON table1
> FOR UPDATE
> AS
> BEGIN
> IF UPDATE(colum1)
> SELECT
> i.colum1
> FROM inserted i
> IF i.column1 = '100'
> UPDATE table1
> SET column2 = 'value'
> END
>
> I'm not sure if this is how the syntax is supposed to look, I keep getting a
> message saying error in syntax near '100'. Please... any help will be GREATLY
> APPRECIATED.
> Thanks.
> --
> CipherTeKST
> MCSE: Security 2003, CCNA, Security+



CREATE TRIGGER trigger1
ON table1
FOR UPDATE
AS
BEGIN
--IF (COLUMNS_UPDATED() & power(2,(1-1))) > 0
IF (COLUMNS_UPDATED() & 1 ) > 0
SELECT
i.colum1
FROM inserted i
IF i.column1 = '100'
UPDATE table1
SET column2 = 'value'
END

Codeman

3/23/2007 1:16:00 AM

0



"CipherTeKST" wrote:

> I am trying to create a trigger that will update a second column once a first
> column is updated in the same row. For example I have a table with several
> columns, I need for the trigger to check column1 to see if the integer has
> reached 100 if so then update column2 in the same record. What I have so far
> is...
> CREATE TRIGGER trigger1
> ON table1
> FOR UPDATE
> AS
> BEGIN
> IF UPDATE(colum1)
> SELECT
> i.colum1
> FROM inserted i
> IF i.column1 = '100'
> UPDATE table1
> SET column2 = 'value'
> END
>
> I'm not sure if this is how the syntax is supposed to look, I keep getting a
> message saying error in syntax near '100'. Please... any help will be GREATLY
> APPRECIATED.
> Thanks.
> --
> CipherTeKST
> MCSE: Security 2003, CCNA, Security+


You need to save the value of the i.column1 from your select clause because
the i.column1 is not still valid for the if statement.

CREATE TRIGGER trigger1
ON table1
FOR UPDATE
AS
BEGIN
Declare @Inserted Integer
IF UPDATE(colum1)
SELECT
@Inserted = i.colum1
FROM inserted i
IF @Inserted = 100
UPDATE table1
SET column2 = 'value'
END


AK

3/23/2007 1:53:00 AM

0

On Mar 22, 8:16 pm, Codeman <Code...@discussions.microsoft.com> wrote:
> "CipherTeKST" wrote:
> > I am trying to create a trigger that will update a second column once a first
> > column is updated in the same row. For example I have a table with several
> > columns, I need for the trigger to check column1 to see if the integer has
> > reached 100 if so then update column2 in the same record. What I have so far
> > is...
> > CREATE TRIGGER trigger1
> > ON table1
> > FOR UPDATE
> > AS
> > BEGIN
> > IF UPDATE(colum1)
> > SELECT
> > i.colum1
> > FROM inserted i
> > IF i.column1 = '100'
> > UPDATE table1
> > SET column2 = 'value'
> > END
>
> > I'm not sure if this is how the syntax is supposed to look, I keep getting a
> > message saying error in syntax near '100'. Please... any help will be GREATLY
> > APPRECIATED.
> > Thanks.
> > --
> > CipherTeKST
> > MCSE: Security 2003, CCNA, Security+
>
> You need to save the value of the i.column1 from your select clause because
> the i.column1 is not still valid for the if statement.
>
> CREATE TRIGGER trigger1
> ON table1
> FOR UPDATE
> AS
> BEGIN
> Declare @Inserted Integer
> IF UPDATE(colum1)
> SELECT
> @Inserted = i.colum1
> FROM inserted i
> IF @Inserted = 100
> UPDATE table1
> SET column2 = 'value'
> END

Be careful:

UPDATE table1
SET column2 = 'value'

will update the whole table. Add a WHERE clause.

Tibor Karaszi

3/23/2007 7:21:00 AM

0

Also, the suggested trigger doesn't handle multi-row updates.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://sqlblog.com/blogs/tib...


"Alex Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
news:1174614771.535060.87600@n59g2000hsh.googlegroups.com...
> On Mar 22, 8:16 pm, Codeman <Code...@discussions.microsoft.com> wrote:
>> "CipherTeKST" wrote:
>> > I am trying to create a trigger that will update a second column once a first
>> > column is updated in the same row. For example I have a table with several
>> > columns, I need for the trigger to check column1 to see if the integer has
>> > reached 100 if so then update column2 in the same record. What I have so far
>> > is...
>> > CREATE TRIGGER trigger1
>> > ON table1
>> > FOR UPDATE
>> > AS
>> > BEGIN
>> > IF UPDATE(colum1)
>> > SELECT
>> > i.colum1
>> > FROM inserted i
>> > IF i.column1 = '100'
>> > UPDATE table1
>> > SET column2 = 'value'
>> > END
>>
>> > I'm not sure if this is how the syntax is supposed to look, I keep getting a
>> > message saying error in syntax near '100'. Please... any help will be GREATLY
>> > APPRECIATED.
>> > Thanks.
>> > --
>> > CipherTeKST
>> > MCSE: Security 2003, CCNA, Security+
>>
>> You need to save the value of the i.column1 from your select clause because
>> the i.column1 is not still valid for the if statement.
>>
>> CREATE TRIGGER trigger1
>> ON table1
>> FOR UPDATE
>> AS
>> BEGIN
>> Declare @Inserted Integer
>> IF UPDATE(colum1)
>> SELECT
>> @Inserted = i.colum1
>> FROM inserted i
>> IF @Inserted = 100
>> UPDATE table1
>> SET column2 = 'value'
>> END
>
> Be careful:
>
> UPDATE table1
> SET column2 = 'value'
>
> will update the whole table. Add a WHERE clause.
>

bob

3/23/2007 11:39:00 AM

0

This works:

USE tempdb
GO

DROP TABLE table1
GO
CREATE TABLE table1 ( id INT PRIMARY KEY, column1 VARCHAR(5), column2
VARCHAR(5) )
GO

CREATE TRIGGER trigger1
ON table1
FOR UPDATE
AS

BEGIN
IF UPDATE(column1)
BEGIN
UPDATE t
SET t.column2 = 'value'
FROM table1 t
INNER JOIN inserted u ON t.id =u.id
WHERE t.column1 = '100'
END

END
GO

INSERT INTO table1 SELECT 1, '99', 0
INSERT INTO table1 SELECT 2, '99', 0

SELECT *
FROM table1

UPDATE table1 SET column1 = '100'
WHERE id = 1

SELECT *
FROM table1

Let me know how you get on.

wBob

"CipherTeKST" wrote:

> I am trying to create a trigger that will update a second column once a first
> column is updated in the same row. For example I have a table with several
> columns, I need for the trigger to check column1 to see if the integer has
> reached 100 if so then update column2 in the same record. What I have so far
> is...
> CREATE TRIGGER trigger1
> ON table1
> FOR UPDATE
> AS
> BEGIN
> IF UPDATE(colum1)
> SELECT
> i.colum1
> FROM inserted i
> IF i.column1 = '100'
> UPDATE table1
> SET column2 = 'value'
> END
>
> I'm not sure if this is how the syntax is supposed to look, I keep getting a
> message saying error in syntax near '100'. Please... any help will be GREATLY
> APPRECIATED.
> Thanks.
> --
> CipherTeKST
> MCSE: Security 2003, CCNA, Security+