SQL trigger change data in inserted table??

How can I change data in inserted table before MS SQL insert data from inserted table to real table?
Thanks for any helping
hungxadieuAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Brendt HessConnect With a Mentor Senior DBACommented:
What version of SQL Server?  

Assuming SQL2K or up, use an INSTEAD OF INSERT Trigger (look in BoL for information on this type of trigger).  Substitute the data you need for the data you get.
0
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
you would need to create an INSTEAD OF INSERT trigger.
0
 
hungxadieuAuthor Commented:
Can you give me a simple example?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
brigzyCommented:
You could use an AFTER UPDATE trigger.

I think it is an alternative solution to consider because an INSTEAD OF trigger has wide ranging maintenance issues: ie when a new field is added the INSTEAD OF trigger must allways be modified.
Here is my example:
I want to blank out a Solictor and PartyType fields when a Guardian field has been assigned .  (see code snippet)
Note : I have recursive triggers Disabled
Cheers
Brigzy

ALTER TRIGGER [dbo].[trg_usrChildren_Update]
   ON  [dbo].[Usr_Children] 
   AFTER UPDATE
AS 
BEGIN
	-- If Guardian set - blank out Solictor and PartyType
	UPDATE usr_Children
	SET Solicitor = NULL, PartyType = NULL
	FROM usr_Children uc INNER JOIN Inserted i ON uc.ID = i.ID
	WHERE RTRIM(ISNULL(i.GuardianAdLitem,'')) <> ''
END

Open in new window

0
 
brigzyCommented:

Additional note :
Just to explain that the same above method can be applied to an AFTER INSERT, as more relevent to your initial question.
Cheers
Brigzy

ALTER TRIGGER [dbo].[trg_usrChildren_AfterInsert]
   ON  [dbo].[Usr_Children] 
   AFTER INSERT
AS 
BEGIN
	-- If Guardian set - blank out Solictor and PartyType
	UPDATE usr_Children
	SET Solicitor = NULL, PartyType = NULL
	FROM usr_Children uc INNER JOIN Inserted i ON uc.ID = i.ID
	WHERE RTRIM(ISNULL(i.GuardianAdLitem,'')) <> ''
END

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.