Need Help with Triggers - 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)

Need Help with Triggers

Author

Topic

Diesel00
Starting Member

6 Posts

Posted - 2014-01-21 : 11:06:49

Hello,

I have to create a Trigger that looks at the LgID & FRID in 2 different tables which are Proposals & Logins and checks to make sure the FRID for the LgID on the 2 tables are the same FRID before I can insert the LgID into the ProposalProfitSplitWorksheet Table Column CreatedByUSerID.

Any help would be appreciated

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-22 : 07:34:55

Sorry not fullt clear. As per what I understood you just need to define foreign key relationships on table ProposalProfitSplitWorksheet on LgID to point to Logins.LgID and similarly for FRID

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

Diesel00
Starting Member

6 Posts

Posted - 2014-01-22 : 07:45:59

I need a trigger on a table that looks at 2 different tables First table Logins: it will look at the LgId and FRID columns. Sencond table it needs to look at Table Proposals: and look at the FRID and make sure that the 2 frids are the same before the insert statement into Table Profit: Column UserID can be written to.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-22 : 08:36:55

So does your profit table has fields LgId and FRID ?

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

Diesel00
Starting Member

6 Posts

Posted - 2014-01-22 : 09:18:08

SELECT [WorksheetID]
,[DateTimeCreated]
,[CreatedByUserID]
,[CreatedByImpersonatingUserID]
,[ProposalUniqueID]
,[ScenarioNumber]
,[BillingBranchID]
,[SiteSurveyBranchID]
,[DateTimeAcceptedBySellingBranch]
,[AcceptedBySellingBranchUserID]
,[AcceptedBySellingBranchImpersonatingUserID]
,[DateTimeAcceptedByInstallingBranch]
,[AcceptedByInstallingBranchUserID]
,[AcceptedByInstallingBranchImpersonatingUserID]
FROM [101WareTest].[dbo].[ProposalProfitSplitWorksheet]

SELECT TOP 1000 [UniqueID], [PropID], [Rev], [FRID], [NatFRID], [us], [ActiveRev], [COID], [BillToCOID], [CPID], [LeadID],[ProjID], [LgID], [SalesEng]
, [title], [status], [created], [resolution], [resolutiondt], [resolvedby], [PMappDt], [PMappLgID], [CustAppDt], [CustAppLgID], [CustAppCPID], [FinAppDt], [FinAppLgID], [SalesTax], [CustPO], [PricingMethod], [TaxMethod], [PaymentTerms], [TermsConds], [PCClevel], [ProjCloseDt], [ProjCompDt], [SpecConds], [ProjContact], [MasterPermit], [ElectricPermit], [folio], [legal], [OneTime], [AdjRsn], [dirtyPrice], [recentPrice], [recentPandM], [recentLabor], [recentOther], [ptDetail], [isTemplate], [SysList], [rollups], [prefs], [template], [message], [POnotes]
FROM [101WareTest].[dbo].[proposals]

SELECT [LgID], [FRID], [BRID], [firstname], [lastname], [jobtitle], [email], [office], [cellphone], [hidecell], [noDirectory], [username]
, [password], [admin], [owner], [lockoutct1], [lockoutct2], [remlogin], [inactive], [appLimit], [svcLimit], [PropPrefs], [coRollups], [propMessage], [dailyemail], [FwdEmail], [mustChgPW], [BBtoken], [mailBox], [statusDt], [status], [helpdesk], [birthmonth], [birthday], [HomePhone], [HomeAdd], [PayPeriod], [ComDeal], [OvrDeal], [BoxDeal], [BoxOvr], [SvcDeal], [SvcOvr], [Exempt], [Boss], [EmpNo], [Rate], [HWM], [HireDate], [LastRev], [NextRev], [lastView], [lastFilterType], [lastFilterRec], [ipaddress]
FROM [101WareTest].[dbo].[logins]

These are the 3 Tables above. I need to check that the FRID's on the proposals & logins Tables are equal before letting the LgID inserted into the column CreatedByUserID.. I am thinking and If Exist but I am not sure.

Thanks

Diesel00
Starting Member

6 Posts

Posted - 2014-01-22 : 16:51:20

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-22 : 18:02:44

Why would you want to use a trigger when SQL does a perfectly good job with declarative referential integrity? Triggers should rarely be used for this sort of thing.

One, potential, issue with what you are proposing is that will only work on single row inserts/updates. If you try to insert/update more than one row it may not work as you expect.

It seems to me that you are headed down the wrong path. Maybe this is an opportunity to take a step back and rethink things..?

if you do want to continue down your path, I'd suggest you either JOIN Inserted to those other tables or some other method so that it can handle multiple row insert/updates.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-23 : 07:08:30

quote:
Originally posted by Diesel00

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

Please see the last suggestion from Lamprey
I dont think you need this approach at all.
Whats the answer to questions raised?

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

Diesel00
Starting Member

6 Posts

Posted - 2014-01-23 : 08:08:03

I understand what you are say but I don't have any common fields in between the Profit to the proposal, and logins tables except the LgID but it needs to be verify that the proposals FRID and the LgID are equal to each other. I am a beginner with SQL Admin/ TSQL code as well. Please be patient

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-23 : 13:12:14

quote:
Originally posted by Diesel00

I understand what you are say but I don't have any common fields in between the Profit to the proposal, and logins tables except the LgID but it needs to be verify that the proposals FRID and the LgID are equal to each other. I am a beginner with SQL Admin/ TSQL code as well. Please be patient

Thanks

I still didnt understand why you think defining a FK constraint wont solve your issue

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