SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Insert on condition in Trigger


Insert on condition in Trigger

Author
Message
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5182 Visits: 2767
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/
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28583 Visits: 8986
did you try the easy way ... split the trigger in two ?

1 trigger only for inserts
1 trigger only for updates

Johan


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5182 Visits: 2767
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/
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28583 Visits: 8986
- You are still using the deleted object in the insert trigger.

Johan


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5182 Visits: 2767
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/
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28583 Visits: 8986
- 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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28583 Visits: 8986
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5182 Visits: 2767
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/
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28583 Visits: 8986
if that meets the requirement, it's ok.

Johan


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5182 Visits: 2767
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search