SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 SQL Trigger Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 02/27/2014 :  11:10:48  Show Profile  Reply with Quote
I'm a SQL novice and am in need of some help making a trigger...

Patient table
ID = 1001
MRNum = 1000
Name = Tom Smith
Assessment = NULL

Assessment table
ID = 1
Patient_ID = 1001
MRNum = 1000
Name = Tom Smith

When a new record is saved to the ASSESSMENT table I want the trigger to update the "Assessment" field in PATIENT table from NULL to 1 where the Patient_ID in ASSESSMENT is equal to ID in PATIENT.

I tried working this out myself but I'm not getting the syntax right.

-Mike


Mike Brown

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/27/2014 :  14:22:12  Show Profile  Reply with Quote

CREATE TRIGGER Trg_AssessmentCapture
ON ASSESSMENT
AFTER INSERT 
AS
BEGIN
UPDATE p
SET Assessment = 1
FROM Patient p
INNER JOIN INSERTED i
ON i.Patient_ID = p.ID
AND i.MRNum = p.MRNum
WHERE Assessment IS NULL
END



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 02/27/2014 :  15:15:51  Show Profile  Reply with Quote
Thank you! Worked great ...BUT, I ran into an unanticipated problem. This trigger works fine when the value is NULL. However, if the user creates another record in Assessment table then the trigger prevents the insert (because Assessment in patient is already = 1).

I think I need an if/else clause here ... If Assessment is = 0 or NULL make it 1 else do nothing (end)

Mike Brown

Edited by - mikeallenbrown on 02/27/2014 15:18:49
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30190 Posts

Posted - 02/27/2014 :  15:25:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
CREATE TRIGGER	dbo.Trg_AssessmentCapture
ON		dbo.ASSESSMENT
AFTER		INSERT 
AS

SET NOCOUNT ON;

UPDATE		p
SET		p.Assessment = 1
FROM		dbo.Patient AS p
INNER JOIN	INSERTED AS i ON i.Patient_ID = p.ID
			AND i.MRNum = p.MRNum
WHERE		p.Assessment IS NULL
		OR p.Assessment  = 0;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 02/27/2014 :  16:00:11  Show Profile  Reply with Quote
Beautiful! Thank you very much!!

Mike Brown
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/01/2014 :  10:52:31  Show Profile  Reply with Quote
Can be merged into single condition as below

CREATE TRIGGER Trg_AssessmentCapture
ON ASSESSMENT
AFTER INSERT 
AS
BEGIN
UPDATE p
SET Assessment = 1
FROM Patient p
INNER JOIN INSERTED i
ON i.Patient_ID = p.ID
AND i.MRNum = p.MRNum
WHERE NULLIF(Assessment,0) IS NULL
END



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000