Trigger question - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger question

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-08 : 09:52:37

Sissina writes "Hi, I have a question for you: I have to create a trigger upon
update of a record..If a given field is null I have to update ONLY that field (Not the whole column) with a select statement..
How shall I do that? I do not want to update the whole column, only the field in question. thanks, appreciate your response."

Nazim
A custom title

1408 Posts

Posted - 2002-04-08 : 09:58:06

Mostly the name Fields and Columns are used interchangbly. How do you distinguish between them??

Newayz, check for isnull funcion. it should be of some help to you.

--------------------------------------------------------------

sissi
Starting Member

20 Posts

Posted - 2002-04-08 : 13:15:59

NAzim thanks for your reply..Let me explain the problem:
Upon a record insert on a table, if this particular column for that record is null, I will have to update that column only.(For that particular record)

This is an example:
Upon insertion of a record:
if table.field11 = null then
table.field11 = table.field22 + '-' table.filed23
many thanks for your help.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-04-08 : 13:36:46

You need to read up on the inserted and deleted tables in BOL. It might also help to read the if update() section for triggers.

setBasedIsTheTruepath
<O>

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-08 : 13:42:30

i think this is what you are looking for


CREATE TRIGGER myTrigger
ON tableName
FOR INSERT
AS
     UPDATE t SET field11 = field22 + '-' + field23 FROM tableName t INNER JOIN inserted i ON tableName.ID = inserted.ID WHERE i.field11 IS NULL
GO

I think that should do it

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-04-08 : 13:58:55

looking again at your requirement you could avoid the trigger altogether and use a computed column.

setBasedIsTheTruepath
<O>

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-08 : 14:01:22

Either a computed column, or you could use your rule in your insert statement using the CASE command.

sissi
Starting Member

20 Posts

Posted - 2002-04-08 : 15:20:04

Thanks very much everyone for your response, another question:
I set the trigger but when I am trying to insert
a record, the system has trouble updating my record. Just for informaiton before setting a trigger, is there any option to be set for trigger so that it can get fired?
thanks,

quote:

Either a computed column, or you could use your rule in your insert statement using the CASE command.

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-08 : 15:24:57

quote:

Just for informaiton before setting a trigger, is there any option to be set for trigger so that it can get fired

The trigger will fire based upon what happened to the table. Either an INSERT, UPDATE, or DELETE.

The following
CREATE TRIGGER myTrigger
ON tableName
FOR INSERT

will always execute when you issue an INSERT statement to tableName

quote:

the system has trouble updating my record

Please explain. Can you give us any errors that the system is returning to you, if there are any?

sissi
Starting Member

20 Posts

Posted - 2002-04-08 : 15:38:07

I am using a DB applicatio that runs under sql server 7 and is the application db that pops up a message saying "Failed to update"
there is no specific error given. But for some reason the system doesn't like that trigger..
Please explain. Can you give us any errors that the system is returning to you, if there are any?

[/quote]

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-08 : 15:49:18

Why a "Failed to Update" error. Aren't you executing INSERT statements.

What DB Application are you using out of curiosity

sissi
Starting Member

20 Posts

Posted - 2002-04-08 : 16:13:42

The interface is Visual Basic..and this is a proprietary DB application. I just don't understand why the system has trouble updating the record?
Here is what I used:
CREATE TRIGGER TEST ON [tablename]
FOR INSERT
AS
UPDATE t SET field11 = field22 FROM tablename INNER JOIN inserted i ON tablename.ID = inserted.ID WHERE i.field11 IS NULL

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-08 : 16:40:20

It could be a permissions issue. Check whether the users who might access this table have UPDATE privileges. It could be that the VB application only uses stored procedures, or application roles, to do certain operations and has denied direct table access to everyone. If that's the case, consider whether granting UPDATE permissions could become a security problem before you do it.

sissi
Starting Member

20 Posts

Posted - 2002-04-08 : 17:51:14

I think there is something wrong with the TRIGGER..Because I just set
a very simple trigger, so that when a record is inserted, a column gets updated and it did do that but whenever I specify inserted table, etc...it gets confused and refuse to update the record. Any input will be appreciated.
thanks.

quote:

It could be a permissions issue. Check whether the users who might access this table have UPDATE privileges. It could be that the VB application only uses stored procedures, or application roles, to do certain operations and has denied direct table access to everyone. If that's the case, consider whether granting UPDATE permissions could become a security problem before you do it.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-08 : 17:54:42

I just noticed an error, try this:

CREATE TRIGGER TEST ON [tablename]
FOR INSERT
AS
UPDATE t SET field11 = field22
FROM tablename INNER JOIN inserted i
ON tablename.ID = i.ID
WHERE i.field11 IS NULL

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-08 : 17:56:59

CREATE TRIGGER TEST ON [tablename]
FOR INSERT
AS
UPDATE tablename SET field11 = field22 + '-' + field23
FROM tablename INNER JOIN inserted i
ON tablename.ID = i.ID
WHERE i.field11 IS NULL

the UPDATE t would have failed

Edited by - yakoo on 04/08/2002 17:59:17

sissi
Starting Member

20 Posts

Posted - 2002-04-09 : 12:47:05

Thanks both Rob and Yakoo for the repsonse. This works to some extent.
WHen I set the field11-'TEST' it works great but when I set it to
field22 the syntax check comes back saying Ambiguos column field22..
Any idea?

quote:

I just noticed an error, try this:

CREATE TRIGGER TEST ON [tablename]
FOR INSERT
AS
UPDATE t SET field11 = field22
FROM tablename INNER JOIN inserted i
ON tablename.ID = i.ID
WHERE i.field11 IS NULL

sissi
Starting Member

20 Posts

Posted - 2002-04-09 : 14:28:16

Guys last question, Please look at the following trigger and let me
know if is Ok to use it just like that:

CREATE TRIGGER [TEST] ON [tablename]
FOR INSERT
AS
UPDATE tablename SET field1 = (SELECT (field2 + left(right(inputdate,12),4) + '-'+ field3+'-'+field4 +'-'+right(dockno,6)) ) FROM tablename

WHERE field1 IS NULL

this trigger will be used when a record is inserted, so there is no point in doing comparaison with inserted I..
when I use INNER JOIN inserted I, I get lots of syntax error..but when
I use the trigger as stated above, I get the answer..Just want to make
sure if is all right to use it like that. Thanks.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-09 : 19:04:32

I would not recommend it. What your trigger does, when you leave out the JOIN to INSERTED, is update EVERY record that has Field 1 is NULL, without regard for whether that record was the one just inserted. Maybe conceptually that sounds fine because you think you never want Field 1 to be null, and this will act as a catch-all, but in my opinion, you are merely masking the fact that there may be an error in another part of the system.

BTW, you keep writing a trigger FOR INSERT and then talk about UPDATING the record. Do you perhaps need to write the trigger FOR INSERT, UPDATE ?

------------------------
GENERAL-ly speaking...

indram
Starting Member

6 Posts

Posted - 2002-04-09 : 21:10:38

This is a rule on UPDATE clause.

UPDATE table_name SET column_name = expression

the column_name always refer to table_name so it isn't necessary to write (you can't do it anyway) like this:

UPDATE table_name SET table_name.column_name = expression

On the other hand, the expression part works like the select clause with join tables. If there is more than one column with same name you have to specify the source. So your field22 should be written either tablename.field22 or i.field22

quote:

Thanks both Rob and Yakoo for the repsonse. This works to some extent.
WHen I set the field11-'TEST' it works great but when I set it to
field22 the syntax check comes back saying Ambiguos column field22..
Any idea?

Edited by - indram on 04/09/2002 21:12:30

Nazim
A custom title

1408 Posts

Posted - 2002-04-10 : 00:28:38

Hi Sisi,

you can try something like this too for mulitple columns avoiding the where condition

update t set t.field11=isnull(t.field11,i.field22),t.field12=isnull(t.field12,i.field23) from tablename t inner join inserted i
on t.id=i.id

HTH

--------------------------------------------------------------

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources