Dieselbf2 (1/22/2014)
Sean Lange (1/22/2014)
Dieselbf2 (1/22/2014)
Here is what I have so far. I am just trying to get the trigger to check the 2 frid that are on different tables before inserting the LgID into the [dbo].[ProposalProfitSplitWorksheet table:Am I on the right track
CREATE TRIGGER [UProposalProfitSplitWorksheet]
ON [dbo].[ProposalProfitSplitWorksheet]
FOR INSERT, UPDATE
AS
Declare @lfrid int
Declare @PFRID int
Select @lfrid = (Select L.FRID From Inserted As I Join logins As L On I.lgID = L.LgID)
Select @PFRID = (Select P.FRID From Inserted As I Join proposals As L On I.LgID = P.LgID)
Thanks
This might be somewhat ok with one MAJOR exception. It cannot handle multiple row inserts or updates.
Consider what value would be in @lfrid if someone ran the following update statement.
UPDATE ProposalProfitSplitWorksheet
set FRID = 9
where LgID in (1,2,3,4,5,6)
I will reiterate that trying to use a trigger for RI is the wrong methodology. My guess is that you want to create a function that you can use in a check constraint on the FRID column.
If I did it the way you are talking about, then every time my company got a new employee I would have to add the LgID and we have 39 FRID's.
Any help is appreciated
No you missed my point. Your trigger that you are working will not handle multiple row inserts/updates. When writing triggers you need to be careful to build things set based so you can handle that.
When you have a variable equal to a column in INSERTED you can't handle that.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/