problem writing trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problem writing trigger

Author

Topic

vijayinani
Starting Member

3 Posts

Posted - 2009-04-14 : 03:07:30

Hi,

I have a table - TEMP_TABLE
The columns are as follows: COL1, COL2, COL3, COL4, COL5
COL1 is the primary key.

I want to write a trigger using the following conditions:
1. If the COL3 is updated with 'U' and the old value of COL3 is not 'U', only then the COL4 should be updated with 'NULL'
2. If any other column other than COL3 is updated, then the trigger should not run.
3. The trigger should only affect the rows and not the entire table for COL4 i.e. if the COL3 is updated for row No. 5 from 'Z' to 'U', then the COL4 of the same row should be updated with NULL and not the entire COL4 of the table.

Is the data enough? Kindly ask if more info. is needed.
Kindly reply.
I have never written a trigger. Kindly reply with an example.

Thanks and Regards,
Vijay :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-14 : 03:14:36

[code]CREATE TRIGGER dbo.trgTemp
ON dbo.Temp_Table
AFTER UPDATE
AS

SET NOCOUNT ON

IF UPDATE(Col3)
UPDATE tt
SET tt.Col4 = NULL
FROM dbo.Temp_Table AS tt
INNER JOIN inserted AS i ON i.Col1 = tt.Col1
INNER JOIN deleted AS d ON d.Col1 = tt.Col1
WHERE i.Col3 = 'U'
AND d.Col3 <> 'U'[/code]

E 12°55'05.63"
N 56°04'39.26"

vijayinani
Starting Member

3 Posts

Posted - 2009-04-14 : 03:23:25

Thank you Peso!
Will let u know the outcome.

Regards,
Vijay.

vijayinani
Starting Member

3 Posts

Posted - 2009-04-14 : 04:29:11

Can you pls explain me how this trigger works. I didnt understood the following points:

1. SET NOCOUNT ON
2. What are inserted and deleted tables?
3. does whole of the row is deleted and inserted or only the fields required?

Pls reply if you are free.
Thanks!

Regards,
Vijay.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-15 : 13:30:44

quote:
Originally posted by vijayinani

Can you pls explain me how this trigger works. I didnt understood the following points:

1. SET NOCOUNT ON
this is for suppressing the count of rows affected messages
2. What are inserted and deleted tables?
inserted and deleted tables are internal temporary tables used by sql server. during updates, the old values before update happened will be available in deleted table and new values will be available in inserted table
3. does whole of the row is deleted and inserted or only the fields required?
it copies the contents of inserted table which contains new values to table and values that were in table earlier will be copied onto deleted table

Pls reply if you are free.
Thanks!

Regards,
Vijay.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources