Help with a trigger ... thank you

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

Help with a trigger ... thank you

Author

Topic

AurasD
Starting Member

23 Posts

Posted - 2009-01-12 : 14:40:52

Hello!I have a problem with a trigger.To learn triggers i have created a small database that contains one table with 4 columns:
PID-it's PK autonumber so it's int,
City-nvarchar,
Street-nvarchar,
FA-nvarchar.
I created a trigger so if I insert a value in the Street field I would like that the FA column get the value:'Your street is:'+Street's value.
So the code for the trigger is:


create trigger T
on Adress
after insert,update
as
begin
declare @PID int
select @PID=Scope_Identity()
if update(Street)
select FA='Your street is:'+(select Street from Adress where PID=@PID)
end

The thing is that it doesn't do anything.Can you help me figure why?
Now I know that this can be done with computed columns too,but still as I am learning triggers I would like to know this way,if it's possible.Thank you

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-12 : 14:46:59

This may work (not tested):

create trigger T
on Adress
after insert,update
as
begin
if update(Street)
update a
set FA='Your street is: '+ i.Street
from Adress a
join inserted i on (i.PID = a.PID)
end

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-12 : 14:48:08

And you can read BOL for Triggers regarding auto temp tables INSERTED and DELETED

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

AurasD
Starting Member

23 Posts

Posted - 2009-01-12 : 14:50:13

It's not working.But thank you for trying to help me.OK well if any of you guys have a suggestion please post it.Thank you

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-12 : 14:52:24

What is not working? Is there any error message?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-12 : 14:54:29

Ah - I see. BEGIN and END for the IF-Statement is missing...

No, you're never too old to Yak'n'Roll if you're too young to die.

AurasD
Starting Member

23 Posts

Posted - 2009-01-12 : 14:55:52

No but it doesn't do anything.I mean after the insertion i expected that the value of the FA would be set automatically as 'Your street is'+Street's value but it is still null.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-12 : 15:20:40

I've tested and it's working:

create table test_adress(
PID int identity(1,1),
City nvarchar(255),
Street nvarchar(255),
FA nvarchar(255)
)
go
create trigger T
on test_adress
after insert,update
as
begin
 if update(Street)
 begin
  update a
  set FA='Your street is: '+ i.Street
  from test_adress a
  join inserted i on (i.PID = a.PID)
 end
end
go

select * from test_adress

insert test_adress
select 'Hamburg', 'Grosse Freiheit', null
go
select * from test_adress
go
drop table test_adress

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

AurasD
Starting Member

23 Posts

Posted - 2009-01-12 : 15:31:58

Man it works but I'm using SSMS to enter data in the table.And it says that the data was successfully submitted but there was a problem when retrieving the data back and i have an exclamation mark at the beginning of my inserted row.When i try to delete it is says that a concurrency error control occurred and the row can't be deleted.So how do I make this error go away?Anyway if i close the table and I reopen it all it's OK.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-12 : 15:41:03

That is because your way to enter data is locking the table and the trigger is waiting til you release it and then the trigger can do it's work.
I see no way to get rid of this error as long as you enter data to table in this way.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

AurasD
Starting Member

23 Posts

Posted - 2009-01-12 : 15:52:56

But man I'm intending of using a datagridview in c# to bind this table and I'm afraid it will not work if in SSMS doesn't work.I'll test it tomorrow and let you know if it does.Again thank you for your explanations.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-12 : 16:35:15

Do not use the GUI to modify data. Use DML statements instead: INSERT, UPDATE, DELETE.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-01-12 : 16:37:05

I don't understand why you need a trigger, or why you would want to have "Your street is: " stored in the table.

CODO ERGO SUM

AurasD
Starting Member

23 Posts

Posted - 2009-01-12 : 17:10:46

Excuse me tkizer but what do you mean by "do not modify data using GUI".
I mean for each row in the datagridview the DataAdapter calls the insert,update or delete command(s).So it uses the DML statements.And in a GUI program how it's possible to modify the data if not in the interface?I update the data by calling the Update() method of the data adapter.Anyway if you can give me some tips regarding other methods or how you update the data in your GUI programs I would appreciate it very much.And thank you all for the reply's guys.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-12 : 17:13:22

I'm referring to how you are testing this in SSMS. Use DML statements instead of the SSMS GUI.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

AurasD
Starting Member

23 Posts

Posted - 2009-01-12 : 17:31:44

O please, I have an old question and maybe you can help me.I've posted on windows forms forum but got no answer.I had a table which had a string PK so it was not identity.Then I binded the tabel to a datagridview in a program in C#.All worked well unless I tried to update the PK through the datagridview.When i tried to save ,after I've modified the PK in the datagridview,i got an error saying something about concurrency violation.I know I need 2 PK's for this,an old one and a new one but can you give me some ideas on how to get the old one.I was thinking something like this(for example a table called Animals with a string PK meaning the animal genre):


Update Animals
set AnimalGenre=@newAnimalGenre
//other columns
where AnimalGenre=@oldAnimalGenre

But how can i get the old PK because I modified it in the datagridview so now it has become the new one.Thanks again for your advices,I'll use them from now on wisely.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-12 : 17:39:54

I don't have any experience with C# and datagridviews. I'm a SQL Server DBA, not a developer. Perhaps you should post your question in our ASP.NET forum here or even try www.asp.net

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

AurasD
Starting Member

23 Posts

Posted - 2009-01-12 : 17:51:46

OK,thanx anyway tkizer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 23:45:02

quote:
Originally posted by AurasD

O please, I have an old question and maybe you can help me.I've posted on windows forms forum but got no answer.I had a table which had a string PK so it was not identity.Then I binded the tabel to a datagridview in a program in C#.All worked well unless I tried to update the PK through the datagridview.When i tried to save ,after I've modified the PK in the datagridview,i got an error saying something about concurrency violation.I know I need 2 PK's for this,an old one and a new one but can you give me some ideas on how to get the old one.I was thinking something like this(for example a table called Animals with a string PK meaning the animal genre):

you can get old and new values by writing a trigger. Inside trigger code use internal tables INSERTED and DELETED to get old and new values.
IF you're using sql 2005 you can use OUTPUT operator also to get INSERTED,DELETED values
Update Animals
set AnimalGenre=@newAnimalGenre
//other columns
where AnimalGenre=@oldAnimalGenre

But how can i get the old PK because I modified it in the datagridview so now it has become the new one.Thanks again for your advices,I'll use them from now on wisely.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-13 : 03:25:50

quote:
Originally posted by Michael Valentine Jones

I don't understand why you need a trigger, or why you would want to have "Your street is: " stored in the table.
CODO ERGO SUM

First post:

quote:
To learn triggers i have created a small database that contains ...

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

AurasD
Starting Member

23 Posts

Posted - 2009-01-13 : 07:29:10

Guys I've solved it!I've received answer on the Windows Forms Data Controls and Databinding forum and it worked.Thanx all!And one more thing:is there any way I can mark this topic as closed or something like because I found the solution?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources