Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

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?
0
ASIADMIN
Asked:
ASIADMIN
  • 5
  • 3
1 Solution
 
chapmandewCommented:
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
 
ZberteocCommented:
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
 
ZberteocCommented:
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
ASIADMINAuthor Commented:
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
 
ASIADMINAuthor Commented:
While the answer wasnt my final solution, it did help me with the cast
0
 
ZberteocCommented:
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
 
ZberteocCommented:
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
 
ASIADMINAuthor Commented:
Chalk it up to me not knowing you could split them.

Can I still split them or is it too late.
0
 
ZberteocCommented:
Next time. ;o)

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now