Insert Trigger 1

[MichaelF81]

I created a trigger for whenever data is inserted into a table

CREATE TRIGGER trgEmIDIns ON [afm].[em] 
FOR INSERT
AS
update afm.em
set em_id = name_last + ', ' + name_first + ' ' + em_id

name_last = varchar(50)
name_first = varchar(50)
em_id = varchar(150)

I ran an insert statement (very simple and basic)

insert into afm.em (name_first, name_last, em_id)
Values ('Mike', 'Fed', '12345678')

into this 3 columns, and I get the following errors

Server: Msg 8152, Level 16, State 9, Procedure trgEmIDIns, Line 4
String or binary data would be truncated.
The statement has been terminated.

I also tried

CREATE TRIGGER trgEmIDIns ON [afm].[em] 
FOR INSERT
AS
update afm.em
set em_id = rtrim(name_last) + ', ' + rtrim(name_first) + ' ' + rtrim(em_id)

And I get the same error

Server: Msg 8152, Level 16, State 9, Procedure trgEmIDIns, Line 4
String or binary data would be truncated.
The statement has been terminated.

"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[ptheriault]

How big is your em_id field on the amf.em table. It looks like the value you are trying to set it to long for the field in the table.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[MichaelF81]

em_id = 150 varchar it is definetly large enough




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[Catadmin]

I don't know if this has anything to do with it, but your Trigger is non-specific for the Update Statement. You're literally updating every last row in the table with a new Emp_ID of the last value that someone attempted to insert.

BTW, I tested your code and I'm not running into this error. So my best guess is that you have data already in this table that is causing your problem.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[MichaelF81]

There is already data in the table. How would I update this to check existing data and only update the new stuff?




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[Catadmin]

You need to add a WHERE clause to your Update statement. Since I don't know what data you want to change, I can't advise you as to what you're checking in the WHERE clause, but that's all you'll need to do.

As far as the error message goes, I'm betting you have a record where one of your fields is at the maximum varchar() limit. Since an Update statement first checks for the biggest record before performing the update, it'll error out and ignore the fact that the record you're updating doesn't necessarily get that big. (This has caused me many problems in the past).

You'll have to do an audit of your data (use LEN() to find the errant record) and then increase the size of the appropriate column OR fix the record(s) in question.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

You need to add a WHERE clause to your Update statement. Since I don't know what data you want to change, I can't advise you as to what you're checking in the WHERE clause, but that's all you'll need to do.

As far as the error message goes, I'm betting you have a record where one of your fields is at the maximum varchar() limit. Since an Update statement first checks for the biggest record before performing the update, it'll error out and ignore the fact that the record you're updating doesn't necessarily get that big. (This has caused me many problems in the past).

You'll have to do an audit of your data (use LEN() to find the errant record) and then increase the size of the appropriate column OR fix the record(s) in question.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

Yikes! Sorry for the double-post. I lost my browser window and thought my reply hadn't posted. Forgive please...



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[MichaelF81]

I would rather have 2 posts instead of none.

Thanks, I will try this out once I finish with a few reports.




"Adults are just obsolete children and the hell with them." - Dr. Seuss