Trigger ??

Question

Trigger ??

bharathi-903755

SSC-Addicted

Points: 425
More actions
December 4, 2008 at 7:02 am

#196694

Hi
I am trying to insert data from table A to table B.
I need a trigger on table B to check the no. of characters in columns and if it is > 512 it shud get truncated to 512 and then insert into table B....
Can anyone help me on this?
Regards,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

If you needed a trigger at all, it would be on Table A, as that is where you are inserting your data.

However, situations like this are normally handled by what's doing the insert. Is your insert happening via a stored procedure? If so, you can just perform the check there.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

bharathi-903755 SSC-Addicted Points: 425 More actions · Answer 2

yes..Insertion is happening through a stored procedure.

Table A can hold the data more than 512 chars.. i need trigger on Table B as data is coming into Table B from Table A...

Hope im clear...

steveb. SSC-Forever Points: 46733 More actions · Answer 3

If you are using a stored proc for all the inserts against the table, then it would be better to modify this procedure to check the number of records rather than using a trigger.

Garadin One Orange Chip Points: 29613 More actions · Answer 4

Unfortunately, your'e not.

This is the way I understand your setup, please correct me where wrong:

You are inserting data into tableA.

If that data is more than 512 characters, you would like to truncate that information, and insert into tableB.

If the data is not more than 512 characters, you would not insert into table B and leave it in only tableA.

Am I correct in this assessment?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Seth is right. You want the process that takes the data from tableA and inserts it into tableB to do the truncation. Why? Because the default trigger type in SQL Server is an AFTER trigger. This means that the trigger will take place after the insert and since the column in tableB will only accept up to 512 characters an error will occur before the insert, thus the trigger will never fire. You might, and I have not tested this, be able to use an INSTEAD OF trigger which does the truncation and then re-issues the insert statement.

As I stated originally, Seth is right, the process that transfers the data from tableA to tableB should do the truncation.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

vaseem-773819 Ten Centuries Points: 1015 More actions · Answer 6

Hi

No I dont think so

I think you are trying to insert data from table A to table B

if any column's data is > 512 character then you need to truncate the data upto 512 character then insert into table B and if data is already <= 512 character then you will not truncate the data just insert.

I think the best method is to do this truncation at stored procedure level instead of using instead of Trigger.

Regards,

Vaseem