I added a column that is set aside for a random number, the random number is generated in the trigger to all rows, which then triggers my calculation fire on...

If a trigger fires, it works assuming people enter the data each week in order ... if they go back and edit a previous week, it breaks the integrity for...

lag([Occupied Suites]) OVER (ORDER BY campus, building, week)

This is the part I ended up using ... the trigger then takes the value and adds it into the current...

Below is the source table, the view, and the trigger on the source table.

What I am finding out now (and someone mentioned before with caution :)) is that any retroactive...

I ended up creating a view using similar logic to MMartins1, then build a trigger on the main table to reference the view. It works nicely. 🙂

Thanks everyone for your...

Thanks MMartin1, I am trying to use your query in an editable view. The only draw back I saw to the query (which I think I can resolve) is the...

I keep getting a "Cannot create index on view "TABLE NAME" because it contains a ranking or aggregate window function. Remove the function from the view definition or, alternatively, do...

The data is editable by the end-user - they can't directly edit a view can they? If yes, then my problem is solved.

Thanks Mark, do you know how I would use this as a trigger? The reason i need to use the trigger, is the lightswitch application is just reading the source...

Any thoughts? I feel like I am really close... 🙁

CREATE TABLE [dbo].[Census Tracker](

[Campus] [nvarchar](50) NOT NULL,

[Building] [nvarchar](50) NOT NULL,

[Week] [date] NOT NULL,

[Total Suites] [int] NULL,

[Rentable Suites] [int] NULL,

[Occupied Suites] [int] NULL,

[Resident Total] [int] NULL,

[Notes] [nvarchar](500) NULL

)

GO

INSERT INTO [Census Tracker]

VALUES...

I've made a bit more progress ...

USE [ERDB]

GO

/****** Object: Trigger [dbo].[trig_OccupiedSuites] Script Date: 3/10/2015 10:38:38 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[trig_OccupiedSuites] ON [dbo].[Census...

After reading some on the syntax and use, I came up with the following.

CREATE TRIGGER dbo.trig_OccupiedSuites

ON [dbo].[MyTable]

AFTER UPDATE

AS

BEGIN

-- Your SQL goes here

...

Do you have a sample you would be willing to share? (Stored proc working with csv?) I do SSIS CSV imports all the time!! I didn't know i could avoid...

Thanks Sean,

That is where I am stumped - i have never created a trigger before. Might you be able to share a sample with the proper syntax? I wills still...