asked on

Basic Trigger Question

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?

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zberteoc

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

Zberteoc

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

ASIADMIN

ASKER

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

ASIADMIN

ASKER

While the answer wasnt my final solution, it did help me with the cast

Zberteoc

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

Zberteoc

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

ASIADMIN

ASKER

Chalk it up to me not knowing you could split them.

Can I still split them or is it too late.

Zberteoc

Next time. ;o)

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