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.
Here are the other 2 tables
USE [101WareTest]
GO
/****** Object: Table [dbo].[proposals] Script Date: 1/22/2014 4:57:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[proposals](
[UniqueID] [int] IDENTITY(1000,1) NOT NULL,
[PropID] [int] NOT NULL,
[Rev] [int] NOT NULL,
[FRID] [int] NOT NULL,
[NatFRID] [int] NOT NULL,
[us] [bit] NOT NULL,
[ActiveRev] [bit] NOT NULL,
[COID] [int] NOT NULL,
[BillToCOID] [int] NOT NULL,
[CPID] [int] NOT NULL,
[LeadID] [int] NOT NULL,
[ProjID] [int] NOT NULL,
[LgID] [int] NOT NULL,
[SalesEng] [int] NOT NULL,
[title] [nvarchar](100) NOT NULL,
[status] [int] NOT NULL,
[created] [datetime] NOT NULL,
[resolution] [int] NOT NULL,
[resolutiondt] [date] NULL,
[resolvedby] [int] NOT NULL,
[PMappDt] [date] NULL,
[PMappLgID] [int] NOT NULL,
[CustAppDt] [date] NULL,
[CustAppLgID] [int] NOT NULL,
[CustAppCPID] [int] NOT NULL,
[FinAppDt] [datetime] NULL,
[FinAppLgID] [int] NOT NULL,
[SalesTax] [int] NOT NULL,
[CustPO] [nvarchar](50) NULL,
[PricingMethod] [int] NOT NULL,
[TaxMethod] [tinyint] NOT NULL,
[PaymentTerms] [int] NOT NULL,
[TermsConds] [int] NOT NULL,
[PCClevel] [int] NOT NULL,
[ProjCloseDt] [date] NULL,
[ProjCompDt] [date] NULL,
[SpecConds] [int] NOT NULL,
[ProjContact] [int] NOT NULL,
[MasterPermit] [nvarchar](50) NULL,
[ElectricPermit] [nvarchar](50) NULL,
[folio] [nvarchar](100) NULL,
[legal] [nvarchar](1000) NULL,
[OneTime] [decimal](10, 2) NULL,
[AdjRsn] [int] NOT NULL,
[dirtyPrice] [bit] NOT NULL,
[recentPrice] [decimal](10, 2) NULL,
[recentPandM] [decimal](10, 2) NULL,
[recentLabor] [decimal](10, 2) NULL,
[recentOther] [decimal](10, 2) NULL,
[ptDetail] [nvarchar](1000) NULL,
[isTemplate] [bit] NOT NULL,
[SysList] [nvarchar](100) NULL,
[rollups] [char](14) NOT NULL,
[prefs] [nvarchar](50) NULL,
[template] [int] NOT NULL,
[message] [nvarchar](100) NULL,
[POnotes] [nvarchar](1000) NULL,
CONSTRAINT [PK_proposals] PRIMARY KEY CLUSTERED
(
[UniqueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_proposals] UNIQUE NONCLUSTERED
(
[PropID] ASC,
[Rev] ASC,
[FRID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_NatFRID] DEFAULT ((0)) FOR [NatFRID]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_BillToCOID] DEFAULT ((0)) FOR [BillToCOID]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_ProjID] DEFAULT ((0)) FOR [ProjID]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_SalesEng] DEFAULT ((0)) FOR [SalesEng]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_status] DEFAULT ((0)) FOR [status]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_created] DEFAULT (getdate()) FOR [created]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_resolution] DEFAULT ((0)) FOR [resolution]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_resolvedby] DEFAULT ((0)) FOR [resolvedby]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_PMappLgID] DEFAULT ((0)) FOR [PMappLgID]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_CustAppLgID] DEFAULT ((0)) FOR [CustAppLgID]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_CustAppCPID] DEFAULT ((0)) FOR [CustAppCPID]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_FinAppLgID] DEFAULT ((0)) FOR [FinAppLgID]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_SalesTax] DEFAULT ((0)) FOR [SalesTax]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_PaymentTerms] DEFAULT ((0)) FOR [PaymentTerms]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_TermsConds] DEFAULT ((0)) FOR [TermsConds]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_PCClevel] DEFAULT ((0)) FOR [PCClevel]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_SpecConditions] DEFAULT ((0)) FOR [SpecConds]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_ProjContact] DEFAULT ((0)) FOR [ProjContact]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_AdjRsn] DEFAULT ((-1)) FOR [AdjRsn]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_dirtyPrice] DEFAULT ((1)) FOR [dirtyPrice]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_isTemplate] DEFAULT ((0)) FOR [isTemplate]
GO
ALTER TABLE [dbo].[proposals] ADD CONSTRAINT [DF_proposals_template] DEFAULT ((0)) FOR [template]
GO
ALTER TABLE [dbo].[proposals] WITH CHECK ADD CONSTRAINT [FK_proposals_companies] FOREIGN KEY([COID])
REFERENCES [dbo].[companies] ([COID])
GO
ALTER TABLE [dbo].[proposals] CHECK CONSTRAINT [FK_proposals_companies]
GO
ALTER TABLE [dbo].[proposals] WITH CHECK ADD CONSTRAINT [FK_proposals_logins] FOREIGN KEY([LgID])
REFERENCES [dbo].[logins] ([LgID])
GO
ALTER TABLE [dbo].[proposals] CHECK CONSTRAINT [FK_proposals_logins]
GO
USE [101WareTest]
GO
/****** Object: Table [dbo].[logins] Script Date: 1/22/2014 5:02:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[logins](
[LgID] [int] IDENTITY(1,1) NOT NULL,
[FRID] [int] NOT NULL,
[BRID] [int] NOT NULL,
[firstname] [nvarchar](60) NOT NULL,
[lastname] [nvarchar](60) NOT NULL,
[jobtitle] [nvarchar](50) NOT NULL,
[nvarchar](128) NOT NULL,
[office] [nvarchar](50) NOT NULL,
[cellphone] [nvarchar](50) NOT NULL,
[hidecell] [bit] NOT NULL,
[noDirectory] [bit] NOT NULL,
[username] [nvarchar](128) NOT NULL,
[password] [nvarchar](128) NOT NULL,
[admin] [tinyint] NOT NULL,
[owner] [bit] NOT NULL,
[lockoutct1] [tinyint] NOT NULL,
[lockoutct2] [tinyint] NOT NULL,
[remlogin] [bit] NOT NULL,
[inactive] [bit] NOT NULL,
[appLimit] [int] NOT NULL,
[svcLimit] [int] NOT NULL,
[PropPrefs] [nvarchar](50) NULL,
[coRollups] [char](14) NULL,
[propMessage] [nvarchar](100) NULL,
[dailyemail] [nvarchar](128) NULL,
[FwdEmail] [int] NOT NULL,
[mustChgPW] [bit] NOT NULL,
[BBtoken] [nvarchar](50) NULL,
[mailBox] [bit] NOT NULL,
[statusDt] [datetime] NULL,
[status] [nvarchar](100) NULL,
[helpdesk] [bit] NOT NULL,
[birthmonth] [tinyint] NOT NULL,
[birthday] [tinyint] NOT NULL,
[HomePhone] [nvarchar](50) NULL,
[HomeAdd] [nvarchar](300) NULL,
[PayPeriod] [tinyint] NOT NULL,
[ComDeal] [int] NOT NULL,
[OvrDeal] [int] NOT NULL,
[BoxDeal] [int] NOT NULL,
[BoxOvr] [int] NOT NULL,
[SvcDeal] [int] NOT NULL,
[SvcOvr] [int] NOT NULL,
[Exempt] [bit] NOT NULL,
[Boss] [int] NOT NULL,
[EmpNo] [nvarchar](50) NULL,
[Rate] [decimal](8, 2) NOT NULL,
[HWM] [tinyint] NOT NULL,
[HireDate] [datetime] NULL,
[LastRev] [datetime] NULL,
[NextRev] [datetime] NULL,
[lastView] [int] NOT NULL,
[lastFilterType] [tinyint] NOT NULL,
[lastFilterRec] [int] NOT NULL,
[ipaddress] [nvarchar](24) NULL,
CONSTRAINT [PK_logins] PRIMARY KEY CLUSTERED
(
[LgID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_logins_username] UNIQUE NONCLUSTERED
(
[username] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_BRID] DEFAULT ((0)) FOR [BRID]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_lockout] DEFAULT ((0)) FOR [lockoutct1]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_lockoutct2] DEFAULT ((0)) FOR [lockoutct2]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_remlogin] DEFAULT ((0)) FOR [remlogin]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_inactive] DEFAULT ((0)) FOR [inactive]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_FwdEmail] DEFAULT ((0)) FOR [FwdEmail]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_mustChgPW] DEFAULT ((1)) FOR [mustChgPW]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_helpdesk] DEFAULT ((0)) FOR [helpdesk]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_birthmonth] DEFAULT ((0)) FOR [birthmonth]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_birthday] DEFAULT ((0)) FOR [birthday]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_PayPeriod] DEFAULT ((0)) FOR [PayPeriod]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_ComDeal] DEFAULT ((0)) FOR [ComDeal]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_OvrDeal] DEFAULT ((0)) FOR [OvrDeal]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_BoxDeal] DEFAULT ((0)) FOR [BoxDeal]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_BoxOvr] DEFAULT ((0)) FOR [BoxOvr]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_SvcDeal] DEFAULT ((0)) FOR [SvcDeal]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_SvcOvr] DEFAULT ((0)) FOR [SvcOvr]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_Exempt] DEFAULT ((0)) FOR [Exempt]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_Super] DEFAULT ((0)) FOR [Boss]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_Rate] DEFAULT ((0)) FOR [Rate]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_HWM] DEFAULT ((0)) FOR [HWM]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_lastView] DEFAULT ((0)) FOR [lastView]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_lastFilterType] DEFAULT ((0)) FOR [lastFilterType]
GO
ALTER TABLE [dbo].[logins] ADD CONSTRAINT [DF_logins_lastFilterRec] DEFAULT ((0)) FOR [lastFilterRec]
GO
ALTER TABLE [dbo].[logins] WITH CHECK ADD CONSTRAINT [FK_logins_db_branches] FOREIGN KEY([BRID])
REFERENCES [dbo].[db_branches] ([BRID])
GO
ALTER TABLE [dbo].[logins] CHECK CONSTRAINT [FK_logins_db_branches]
GO
Thank you