[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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
0
hungxadieu
Asked:
hungxadieu
3 Solutions
 
Brendt HessSenior 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 CroweCommented:
you would need to create an INSTEAD OF INSERT trigger.
0
 
hungxadieuAuthor Commented:
Can you give me a simple example?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
SireesCommented:
0
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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