Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Insert on condition in Trigger Expand / Collapse
Author
Message
Posted Tuesday, August 27, 2013 11:51 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 1,953, Visits: 2,397
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/
Post #1489019
Posted Wednesday, August 28, 2013 12:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:21 AM
Points: 6,743, Visits: 8,517
did you try the easy way ... split the trigger in two ?

1 trigger only for inserts
1 trigger only for updates




Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1489025
Posted Wednesday, August 28, 2013 12:52 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 1,953, Visits: 2,397
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/
Post #1489029
Posted Wednesday, August 28, 2013 1:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:21 AM
Points: 6,743, Visits: 8,517
- You are still using the deleted object in the insert trigger.



Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1489030
Posted Wednesday, August 28, 2013 1:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 1,953, Visits: 2,397
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/
Post #1489031
Posted Wednesday, August 28, 2013 1:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:21 AM
Points: 6,743, Visits: 8,517
- 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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1489032
Posted Wednesday, August 28, 2013 1:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:21 AM
Points: 6,743, Visits: 8,517
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1489034
Posted Wednesday, August 28, 2013 1:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 1,953, Visits: 2,397
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/
Post #1489035
Posted Wednesday, August 28, 2013 2:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:21 AM
Points: 6,743, Visits: 8,517
if that meets the requirement, it's ok.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1489061
Posted Wednesday, August 28, 2013 2:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 1,953, Visits: 2,397
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/
Post #1489062
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse