Instead of Triggers - 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

Instead of Triggers

Author

Topic

coolrefche
Starting Member

4 Posts

Posted - 2008-01-23 : 09:07:49

Hi,

I have never written a trigger but need to writye one fairly urgently. I would appreciate some help if possible.

The trigger i need to write need to detect whether inserted or update data for a particular column contains more than 50 chars. If so to truncate data to 50 chars and inserte the data into the required column and remainder (over 50 chars) to place into another column (also 50 chars max).

I guess this needs to be instead of insert, update trigger as both insert or update may take place.

Regards

Refche

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2008-01-23 : 09:09:01

what does truncate mean?

coolrefche
Starting Member

4 Posts

Posted - 2008-01-23 : 09:15:04

Truncate means to shorten.

In my case, if the data that is being inserted or update in a particular column is longer than 50 chars then this column can only accept 50 chars which means that data needs to be truncated/shorten however I do not want to loose remaining data hence will need to insert it into another column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-23 : 11:01:15

do it as follows

CREATE TRIGGER MyTrig ON Table1
INSTEAD OF INSERT,UPDATE
AS

UPDATE t
SET   t.Col=LEFT(i.Col,50),--col is column to be checked for insert/update
t.Col1=SUBSTRING(i.Col,51,LEN(Col)-50)
.........other fields
FROM Table t
INNER JOIN INSERTED i--record exists so update
ON i.pk=t.pk--pk is primary key


INSERT INTO Table (Col,Col1,...other fields)
SELECT LEFT(i.Col,50),SUBSTRING(i.Col,51,LEN(Col)-50),....
FROM INSERTED i
LEFT OUTER JOIN Table t
ON t.pk=i.pk
WHERE t.pk IS NULL --record not exists (insert)

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources