August 27, 2013 at 11:51 pm
Hi,
Here is the code that I have written and its working fine. But now I have to put a condition while Insertion and I am not getting how to do that.
Scenario is: Currently if made any changes DeviationRequestDetails a new row gets inserted into CLCProcessUnitDetailsCompany if it not exists previously. But now what I want is that if I made any change in column DeviationStatus of Source then only new rows should get inserted in Target else if changes made in any other column of Source then no rows should get inserted into Target.
Hope the scenario is clear to you now....
ALTER Trigger [dbo].[Trg_DeviationRequestDetails_Ins]
ON [dbo].[DeviationRequestDetails]
After INSERT, UPDATE AS
BEGIN
Declare @deviationstatus int
SET @deviationstatus = (SELECT D.DeviationStatus from DELETED D)
MERGE INTO CLCProcessUnitDetailsCompany T
USING INSERTED as S
ON S.ProjectID = T.ProjectID AND
S.ProposalID = T.ProposalID AND
S.SolutionId = T.SolutionId AND
S.UnitID = T.UnitID
WHEN MATCHED AND S.DeviationStatus <> @deviationstatus
THEN
UPDATE SET T.DevDateChanged = Getdate()
WHEN NOT MATCHED THEN
INSERT (
ProjectID,
ProposalID,
SolutionID,
UnitID,
DevDateChanged,
QuotDateChanged,
ApprovalDateChanged,
AddedBy,
DateAdded,
ChangedBy,
DateChanged
)
VALUES
(
S.ProjectID,
S.ProposalID,
S.SolutionID,
S.UnitID,
Getdate(),
NULL,
NULL,
S.AddedBy,
S.DateAdded,
S.ChangedBy,
S.DateChanged
);
END
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 28, 2013 at 12:34 am
did you try the easy way ... split the trigger in two ?
1 trigger only for inserts
1 trigger only for updates
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 28, 2013 at 12:52 am
ALZDBA (8/28/2013)
did you try the easy way ... split the trigger in two ?1 trigger only for inserts
1 trigger only for updates
ALTER Trigger [dbo].[Trg_DeviationRequestDetails_Ins]
ON [dbo].[DeviationRequestDetails]
After INSERT AS
BEGIN
Declare @deviationstatus int
SET @deviationstatus = (SELECT D.DeviationStatus from DELETED D)
Declare @count INT
SELECT @count = COUNT(*) FROM INSERTED
MERGE INTO CLCProcessUnitDetailsCompany T
USING INSERTED as S
ON S.ProjectID = T.ProjectID AND
S.ProposalID = T.ProposalID AND
S.SolutionId = T.SolutionId AND
S.UnitID = T.UnitID
WHEN NOT MATCHED AND @count = 1 THEN
INSERT (
ProjectID,
ProposalID,
SolutionID,
UnitID,
DevDateChanged,
QuotDateChanged,
ApprovalDateChanged,
AddedBy,
DateAdded,
ChangedBy,
DateChanged
)
VALUES
(
S.ProjectID,
S.ProposalID,
S.SolutionID,
S.UnitID,
Getdate(),
NULL,
NULL,
S.AddedBy,
S.DateAdded,
S.ChangedBy,
S.DateChanged
)
WHEN NOT MATCHED AND @count > 0 THEN
INSERT
(
ProjectID,
ProposalID,
SolutionID,
UnitID,
DevDateChanged,
QuotDateChanged,
ApprovalDateChanged,
AddedBy,
DateAdded,
ChangedBy,
DateChanged
)
VALUES
SELECT
TOP 1 S.ProjectID,
S.ProposalID,
S.SolutionID,
S.UnitID,
Getdate(),
NULL,
NULL,
S.AddedBy,
S.DateAdded,
S.ChangedBy,
S.DateChanged
FROM S
ORDER BY S.DeviationStatus DESC
;
END
I am getting error in this code...
first INSERT is fine but second INSERT statement is giving error:
Incorrect syntax near the keyword 'SELECT'.
Can you please tell me is this not a correct way?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 28, 2013 at 1:02 am
- You are still using the deleted object in the insert trigger.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 28, 2013 at 1:06 am
OK,
ihave changed the code now...
Removed the MERGE statement.
Is this OK?
ALTER Trigger [dbo].[Trg_DeviationRequestDetails_Ins]
ON [dbo].[DeviationRequestDetails]
After INSERT AS
BEGIN
Declare @count INT
SELECT @count = COUNT(*) FROM INSERTED
If @count = 1
BEGIN
INSERT INTO CLCProcessUnitDetailsCompany
(
ProjectID,
ProposalID,
SolutionID,
UnitID,
DevDateChanged,
QuotDateChanged,
ApprovalDateChanged,
AddedBy,
DateAdded,
ChangedBy,
DateChanged
)
SELECT
S.ProjectID,
S.ProposalID,
S.SolutionID,
S.UnitID,
Getdate(),
NULL,
NULL,
S.AddedBy,
S.DateAdded,
S.ChangedBy,
S.DateChanged
FROM INSERTED S
JOIN CLCProcessUnitDetailsCompany T
ON S.ProjectID = T.ProjectID AND
S.ProposalID = T.ProposalID AND
S.SolutionId = T.SolutionId AND
S.UnitID = T.UnitID
END
ELSE
BEGIN
INSERT INTO CLCProcessUnitDetailsCompany
(
ProjectID,
ProposalID,
SolutionID,
UnitID,
DevDateChanged,
QuotDateChanged,
ApprovalDateChanged,
AddedBy,
DateAdded,
ChangedBy,
DateChanged
)
SELECT
TOP 1 S.ProjectID,
S.ProposalID,
S.SolutionID,
S.UnitID,
Getdate(),
NULL,
NULL,
S.AddedBy,
S.DateAdded,
S.ChangedBy,
S.DateChanged
FROM INSERTED S
JOIN CLCProcessUnitDetailsCompany T
ON S.ProjectID = T.ProjectID AND
S.ProposalID = T.ProposalID AND
S.SolutionId = T.SolutionId AND
S.UnitID = T.UnitID
ORDER BY S.DeviationStatus DESC
END
END
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 28, 2013 at 1:07 am
- I haven't played much with "merge", because of the issues with it I hear from time to time.
I think you should remove the "values" keyword before the select top 1.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 28, 2013 at 1:13 am
What will happen to your trigger if someone inserts data of multiple ProjectID / ProposalID / SolutionId / UnitID in a single batch ?
Keep in mind exceptions do occur !
e.g. uploading a set in bulk or during upgrades
Prepare it to be able to handle sets !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 28, 2013 at 1:15 am
ALZDBA (8/28/2013)
What will happen to your trigger if someone inserts data of multiple ProjectID / ProposalID / SolutionId / UnitID in a single batch ?Keep in mind exceptions do occur !
e.g. uploading a set in bulk or during upgrades
Prepare it to be able to handle sets !
Thats why I put another condition in IF as @count > 1..
In this case it will insert only a single row on basis of max(requestid) from the bulked inserted rows
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 28, 2013 at 2:21 am
if that meets the requirement, it's ok.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 28, 2013 at 2:23 am
ALZDBA (8/28/2013)
if that meets the requirement, it's ok.
But what abt the scenario for which I posted the question...
How can I implement this:
Scenario is: Currently if made any changes in DeviationRequestDetails a new row gets inserted into CLCProcessUnitDetailsCompany if it not exists previously. But now what I want is that if I made any change in column DeviationStatus of Source then only new rows should get inserted in Target else if changes made in any other column of Source then no rows should get inserted into Target.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 28, 2013 at 5:01 am
kapil_kk (8/28/2013)
ALZDBA (8/28/2013)
if that meets the requirement, it's ok.But what abt the scenario for which I posted the question...
How can I implement this:
Scenario is: Currently if made any changes in DeviationRequestDetails a new row gets inserted into CLCProcessUnitDetailsCompany if it not exists previously. But now what I want is that if I made any change in column DeviationStatus of Source then only new rows should get inserted in Target else if changes made in any other column of Source then no rows should get inserted into Target.
You can use the Update() command to test if the column is in the update.
ref: "Testing for UPDATE or INSERT Actions to Specific Columns" http://technet.microsoft.com/en-us/library/ms189799%28v=sql.105%29.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply