SQL Trigger Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

SQL Trigger Help

Author

Topic

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2014-02-27 : 11:10:48

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

52326 Posts

Posted - 2014-02-27 : 14:22:12

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2014-02-27 : 15:15:51

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-27 : 15:25:22

[code]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;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2014-02-27 : 16:00:11

Beautiful! Thank you very much!!

Mike Brown

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-03-01 : 10:52:31

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources