Solved

Basic Trigger Question

Posted on 2008-06-12
9
198 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
ID: 21768477
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
ID: 21769534
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
ID: 21769545
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
ID: 21769642
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Closing Comment

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

Expert Comment

by:Zberteoc
ID: 21769851
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
ID: 21769857
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
ID: 21770519
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
ID: 21770599
Next time. ;o)

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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Login 17 38
T-SQL:  Why can you not SUM on a CASE statement, for crying out loud? 2 29
Update data using formula 22 15
MS SQL Pivot table help 4 5
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

914 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

14 Experts available now in Live!

Get 1:1 Help Now