Solved

Basic Trigger Question

Posted on 2008-06-12
9
197 Views
Last Modified: 2010-04-21
What I would like to do:

string strConstant = "This is the beginning"

When a record is inserted, I want to immediately grab the value of a ntext field and append it to the strConstant variable and then update the ntext field with the vaule of the strConstant.  

I have no experience with triggers and was hoping for some direction.  Should I use an after insert?
can some one give me a little snippet?
0
Comment
Question by:ASIADMIN
  • 5
  • 3
9 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
Comment Utility
create trigger trmytriggername on yourtablename
for insert
as
begin
update t
set somefieldname = somefieldname + cast(ntextfield  as varchar(8000))--may need to use specific text functions to append...will likely have to cast as a varchar
from mytable t
join inserted i on t.fieldname = i.fieldname
join othertable o on t.fieldname = o.fieldname
end
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Try this:

CREATE  TRIGGER trmytriggername ON yourtablename 

FOR INSERT 

AS

BEGIN 

	UPDATE  t SET  

		somefieldname = 'This is the beginning' + cast(ntextfield  as varchar(8000))

	FROM mytable t

	INNER JOIN inserted i 

		ON t.pk_column = i.pk_column -- use the (primary)key column for the table

END 

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Sorry:
CREATE  TRIGGER trmytriggername ON yourtablename 

FOR INSERT 

AS

BEGIN 

	UPDATE  t SET  

		ntextfield = 'This is the beginning' + cast(ntextfield  as varchar(8000))

	FROM mytable t

	INNER JOIN inserted i 

		ON t.pk_column = i.pk_column -- use the (primary)key column for the table

END 

Open in new window

0
 

Author Comment

by:ASIADMIN
Comment Utility
This is what I ended up doing and it works.

Thanks for help
ALTER TRIGGER [dbo].[trgIncidentAfterInsert] ON [dbo].[Incidents] 

 after insert

AS

	declare @incidentid int;

	declare @projectid int;

	

	

	select @incidentid=i.incidentid from inserted i;

	select @projectid=i.projectid from inserted i;
 
 

	BEGIN

		if(@projectid=94) --94 is asi order

		begin

			--set @description = @description + 'test';

			update incidents set description =  'This is the beginning' + (select cast(description as nvarchar(4000)) from incidents where incidentid = @incidentid )  where incidentid = @incidentid;

		

			

		end

		

	END

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Closing Comment

by:ASIADMIN
Comment Utility
While the answer wasnt my final solution, it did help me with the cast
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
I know the answer was already chosen but still:

A much cleaner way to do the exact same thing is posted bellow.

I think that some split points here would have been fair.
ALTER TRIGGER [dbo].[trgIncidentAfterInsert] ON [dbo].[Incidents] 

AFTER INSERT 

AS

BEGIN

	UPDATE inc SET 

		description =  'This is the beginning' + cast(inc.description as nvarchar(4000))

	FROM 

		incidents inc

		INNER JOIN inserted i

			ON inc.incidentid=i.incidentid

			AND inc.projectid=94

END 

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
One correction:
ALTER TRIGGER [dbo].[trgIncidentAfterInsert] ON [dbo].[Incidents] 

AFTER INSERT 

AS

BEGIN

	UPDATE inc SET 

		description =  'This is the beginning' + cast(inc.description as nvarchar(4000))

	FROM 

		incidents inc

		INNER JOIN inserted i

			ON inc.incidentid=i.incidentid

			AND i.projectid=94

END 

Open in new window

0
 

Author Comment

by:ASIADMIN
Comment Utility
Chalk it up to me not knowing you could split them.

Can I still split them or is it too late.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Next time. ;o)

However, you can use the code I posted the last. :o)))
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now