January 22, 2014 at 1:06 pm
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], , [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
January 22, 2014 at 1:16 pm
Hi and welcome to the forums. It is very difficult to figure out what you are trying to do with nothing more than a couple of queries. Can you post the ddl for the tables and a few rows of sample data? This would make it a million times easier to help. I am pretty sure from what you are explaining that you can do this a check constraint instead of a trigger.
_______________________________________________________________
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/
January 22, 2014 at 1:26 pm
Thank you! I think this is what you are asking for.. My Boss wants a Trigger and the reason is maybe to just get me use to writing more logic instead og updat, Delete, and insert along with basic SQL quires as well. Any help would be greatly appreciated
Thank you
USE [101WareTest]
GO
/****** Object: Table [dbo].[ProposalProfitSplitWorksheet] Script Date: 1/22/2014 3:24:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProposalProfitSplitWorksheet](
[WorksheetID] [int] IDENTITY(1,1) NOT NULL,
[DateTimeCreated] [datetime] NOT NULL,
[CreatedByUserID] [int] NOT NULL,
[CreatedByImpersonatingUserID] [int] NULL,
[ProposalUniqueID] [int] NOT NULL,
[ScenarioNumber] [int] NOT NULL,
[BillingBranchID] [int] NOT NULL,
[SiteSurveyBranchID] [int] NOT NULL,
[DateTimeAcceptedBySellingBranch] [datetime] NULL,
[AcceptedBySellingBranchUserID] [int] NULL,
[AcceptedBySellingBranchImpersonatingUserID] [int] NULL,
[DateTimeAcceptedByInstallingBranch] [datetime] NULL,
[AcceptedByInstallingBranchUserID] [int] NULL,
[AcceptedByInstallingBranchImpersonatingUserID] [int] NULL,
CONSTRAINT [PK_ProposalProfitSplitWorksheet] PRIMARY KEY CLUSTERED
(
[WorksheetID] 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
ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] WITH CHECK ADD CONSTRAINT [FK_ProposalProfitSplitWorksheet_db_branches] FOREIGN KEY([BillingBranchID])
REFERENCES [dbo].[db_branches] ([BRID])
GO
ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] CHECK CONSTRAINT [FK_ProposalProfitSplitWorksheet_db_branches]
GO
ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] WITH CHECK ADD CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_AcceptedByInstallingBranchImpersonatingUserID] FOREIGN KEY([AcceptedByInstallingBranchImpersonatingUserID])
REFERENCES [dbo].[logins] ([LgID])
GO
ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] CHECK CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_AcceptedByInstallingBranchImpersonatingUserID]
GO
ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] WITH CHECK ADD CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_AcceptedByInstallingBranchUserID] FOREIGN KEY([AcceptedByInstallingBranchUserID])
REFERENCES [dbo].[logins] ([LgID])
GO
ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] CHECK CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_AcceptedByInstallingBranchUserID]
GO
ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] WITH CHECK ADD CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_AcceptedBySellingBranchImpersonatingUserID] FOREIGN KEY([AcceptedBySellingBranchImpersonatingUserID])
REFERENCES [dbo].[logins] ([LgID])
GO
ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] CHECK CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_AcceptedBySellingBranchImpersonatingUserID]
GO
ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] WITH CHECK ADD CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_AcceptedBySellingBranchUserID] FOREIGN KEY([AcceptedBySellingBranchUserID])
REFERENCES [dbo].[logins] ([LgID])
GO
ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] CHECK CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_AcceptedBySellingBranchUserID]
GO
ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] WITH CHECK ADD CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_CreatedByImpersonatingUserID] FOREIGN KEY([CreatedByImpersonatingUserID])
REFERENCES [dbo].[logins] ([LgID])
GO
ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] CHECK CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_CreatedByImpersonatingUserID]
GO
ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] WITH CHECK ADD CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_CreatedByUserID] FOREIGN KEY([CreatedByUserID])
REFERENCES [dbo].[logins] ([LgID])
GO
ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] CHECK CONSTRAINT [FK_ProposalProfitSplitWorksheet_logins_CreatedByUserID]
GO
ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] WITH CHECK ADD CONSTRAINT [FK_ProposalProfitSplitWorksheet_proposals] FOREIGN KEY([ProposalUniqueID])
REFERENCES [dbo].[proposals] ([UniqueID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] CHECK CONSTRAINT [FK_ProposalProfitSplitWorksheet_proposals]
GO
ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] WITH CHECK ADD CONSTRAINT [CK_ProposalProfitSplitWorksheet_ScenarioNumber] CHECK (([ScenarioNumber]=(2) OR [ScenarioNumber]=(1)))
GO
ALTER TABLE [dbo].[ProposalProfitSplitWorksheet] CHECK CONSTRAINT [CK_ProposalProfitSplitWorksheet_ScenarioNumber]
January 22, 2014 at 2:50 pm
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
January 22, 2014 at 2:54 pm
Dieselbf2 (1/22/2014)
Thank you! I think this is what you are asking for.. My Boss wants a Trigger and the reason is maybe to just get me use to writing more logic instead og updat, Delete, and insert along with basic SQL quires as well. Any help would be greatly appreciatedThank you
That is certainly the ddl. Unfortunately that is for only one table. The other challenge is there are lots of errors when I try to load this on my local instance.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_ProposalProfitSplitWorksheet_db_branches' references invalid table 'dbo.db_branches'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_ProposalProfitSplitWorksheet_db_branches' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_ProposalProfitSplitWorksheet_logins_AcceptedByInstallingBranchImpersonatingUserID' references invalid table 'dbo.logins'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_ProposalProfitSplitWorksheet_logins_AcceptedByInstallingBranchImpersonatingUserID' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_ProposalProfitSplitWorksheet_logins_AcceptedByInstallingBranchUserID' references invalid table 'dbo.logins'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_ProposalProfitSplitWorksheet_logins_AcceptedByInstallingBranchUserID' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_ProposalProfitSplitWorksheet_logins_AcceptedBySellingBranchImpersonatingUserID' references invalid table 'dbo.logins'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_ProposalProfitSplitWorksheet_logins_AcceptedBySellingBranchImpersonatingUserID' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_ProposalProfitSplitWorksheet_logins_AcceptedBySellingBranchUserID' references invalid table 'dbo.logins'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_ProposalProfitSplitWorksheet_logins_AcceptedBySellingBranchUserID' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_ProposalProfitSplitWorksheet_logins_CreatedByImpersonatingUserID' references invalid table 'dbo.logins'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_ProposalProfitSplitWorksheet_logins_CreatedByImpersonatingUserID' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_ProposalProfitSplitWorksheet_logins_CreatedByUserID' references invalid table 'dbo.logins'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_ProposalProfitSplitWorksheet_logins_CreatedByUserID' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_ProposalProfitSplitWorksheet_proposals' references invalid table 'dbo.proposals'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_ProposalProfitSplitWorksheet_proposals' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Obviously you have a little work on the ddl before we can get much going here. Why does your boss ask for this to be done in a trigger? What they are asking for is referential integrity and triggers are not a good choice for that. One issue is you will likely have to write at least 2 triggers for this. An update and an insert. The reason triggers are not good at enforcing RI is because they can be disabled. Let's say I want to force a row in that does not meet the criteria. I simply disable the trigger, insert my data and then enable the trigger.
_______________________________________________________________
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/
January 22, 2014 at 2:57 pm
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.
_______________________________________________________________
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/
January 22, 2014 at 3:01 pm
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
January 22, 2014 at 3:03 pm
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
January 22, 2014 at 3:07 pm
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/
January 22, 2014 at 3:11 pm
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.
[/quote]
So what would I have to change to get this to work as a trigger...
January 22, 2014 at 3:15 pm
Here are the other 2 tables
You need to test the code you post. The idea here is for me to be able to recreate your tables on my system. This has lots of errors.
I removed all the foreign keys and it will at least create the tables.
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
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
CREATE TABLE [dbo].[ProposalProfitSplitWorksheet](
[WorksheetID] [int] IDENTITY(1,1) NOT NULL,
[DateTimeCreated] [datetime] NOT NULL,
[CreatedByUserID] [int] NOT NULL,
[CreatedByImpersonatingUserID] [int] NULL,
[ProposalUniqueID] [int] NOT NULL,
[ScenarioNumber] [int] NOT NULL,
[BillingBranchID] [int] NOT NULL,
[SiteSurveyBranchID] [int] NOT NULL,
[DateTimeAcceptedBySellingBranch] [datetime] NULL,
[AcceptedBySellingBranchUserID] [int] NULL,
[AcceptedBySellingBranchImpersonatingUserID] [int] NULL,
[DateTimeAcceptedByInstallingBranch] [datetime] NULL,
[AcceptedByInstallingBranchUserID] [int] NULL,
[AcceptedByInstallingBranchImpersonatingUserID] [int] NULL,
CONSTRAINT [PK_ProposalProfitSplitWorksheet] PRIMARY KEY CLUSTERED
(
[WorksheetID] 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
So now we have the tables. We still need a few rows of sample to do much here. If I understand correctly you want to make sure you have row in both proposals and logins with a matching FRID? Is there some way to know which row(s) in that table you are interested in? Maybe also LgID must match? I am not quite sure what the business rule is here.
_______________________________________________________________
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/
January 22, 2014 at 3:25 pm
So now we have the tables. We still need a few rows of sample to do much here. If I understand correctly you want to make sure you have row in both proposals and logins with a matching FRID? Is there some way to know which row(s) in that table you are interested in? Maybe also LgID must match? I am not quite sure what the business rule is here.
[/quote]
Just uploaded some sample data
January 22, 2014 at 3:47 pm
Dieselbf2 (1/22/2014)
So now we have the tables. We still need a few rows of sample to do much here. If I understand correctly you want to make sure you have row in both proposals and logins with a matching FRID? Is there some way to know which row(s) in that table you are interested in? Maybe also LgID must match? I am not quite sure what the business rule is here.
Just uploaded some sample data[/quote]
OK cool. I am out of here for now. If I can't get back to this later tonight I will follow up in the morning.
Still need to know what the business rules are. What is your intention for this trigger?
_______________________________________________________________
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/
January 22, 2014 at 5:50 pm
Sean Lange (1/22/2014)
Dieselbf2 (1/22/2014)
So now we have the tables. We still need a few rows of sample to do much here. If I understand correctly you want to make sure you have row in both proposals and logins with a matching FRID? Is there some way to know which row(s) in that table you are interested in? Maybe also LgID must match? I am not quite sure what the business rule is here.Just uploaded some sample data
OK cool. I am out of here for now. If I can't get back to this later tonight I will follow up in the morning.
Still need to know what the business rules are. What is your intention for this trigger?[/quote]
I need the trigger to check the logins table and what ever the Frid for the specfic LgID is, The trigger has to make sure that the same FRID is inthe proposals table for that record that will be getting inserted into the Profit table
January 23, 2014 at 7:35 am
Your sample data won't load. The last row for logins is invalid, not a huge deal I just skipped it.
However, the rows for [ProposalProfitSplitWorksheet] all fail the foreign key constraint for logins. I really need you to test your inserts. Keep in mind that we are all volunteers around here and all the time I spend messing around with being forced to turn identity insert on and off so I can insert identity values and messing around with poorly formed data and syntax is all eating into the time that I can spend on your actual issue.
_______________________________________________________________
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/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply