June 29, 2011 at 7:49 pm
have the following schema:
[dbo].[tbl_Events]
[Event_ID] [uniqueidentifier] NOT NULL (PK)
[Location_ID] [uniqueidentifier] NULL (FK)
[Observation] [nvarchar]
[dbo].[tbl_Locations]
[Event_ID] [uniqueidentifier] NOT NULL
[Location_ID] [uniqueidentifier] NULL (PK)
[Notes] [nvarchar]
Locations is the parent table with a relationship to events through Location ID.
Location_ID is set to be NewID() on create in Locations, and Event_ID is set to be NewID() on create in events. The table, relationship, and PK format is non-changeable due to organizational policy governing replication.
I'm looking for advice on how to define an insert trigger that will create a new row in events, with the location_id pulled from the parent location table, and a new unique event_id. E.g., when (by outside application with no ability to embed sql code) a new location record is created, it gets a location_id of 8170daed-92c8-47f1-98ca-147800329686, and the trigger creates a new event record also with a location_ID of 8170daed-92c8-47f1-98ca-147800329686 and an event_ID of cfed8fe8-b5be-4f78-b366-008672e39637.
June 29, 2011 at 10:48 pm
Can you please share tables creation script so that same can be simulated on my workstation and for better understanding your requirement.
Regards
Ashok
June 30, 2011 at 6:40 am
CREATE TABLE [dbo].[tbl_Events](
[Event_ID] [uniqueidentifier] NOT NULL,
[Location_ID] [uniqueidentifier] NULL,
[Event_Group_ID] [uniqueidentifier] NULL,
[Protocol_Name] [nvarchar](100) NULL,
[Start_Date] [datetime2](0) NULL,
[Start_Time] [datetime2](0) NULL,
[Entered_Date] [datetime2](0) NULL,
[Date_Precision] [nvarchar](50) NULL,
[SpeciesID_Observation] [nvarchar](50) NULL,
[Entered_By] [nvarchar](50) NULL,
[Observation] [nvarchar](max) NULL,
[SpeciesID] [nvarchar](20) NULL,
[Species_ID] [int] NULL,
[OBJECTID] [int] IDENTITY(1,1) NOT NULL,
[Contact_Role] [nvarchar](50) NULL,
[SSMA_TimeStamp] [timestamp] NOT NULL,
CONSTRAINT [tbl_Events$PrimaryKey] PRIMARY KEY CLUSTERED
(
[Event_ID] 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].[tbl_Events] WITH CHECK ADD CONSTRAINT [FK_TBL_EVENTS_TBL_LOCATIONS] FOREIGN KEY([Location_ID])
REFERENCES [dbo].[TBL_LOCATIONS] ([Location_ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tbl_Events] CHECK CONSTRAINT [FK_TBL_EVENTS_TBL_LOCATIONS]
GO
ALTER TABLE [dbo].[tbl_Events] ADD CONSTRAINT [DF_tbl_Events_Event_ID] DEFAULT (newid()) FOR [Event_ID]
GO
############################################################
CREATE TABLE [dbo].[TBL_LOCATIONS](
[OBJECTID] [int] NOT NULL,
[FCategory] [nvarchar](16) NOT NULL,
[MapMethod] [nvarchar](4) NOT NULL,
[HError] [nvarchar](50) NOT NULL,
[MapSource] [nvarchar](255) NULL,
[SourceDate] [datetime] NULL,
[EditDate] [datetime] NOT NULL,
[Notes] [nvarchar](255) NULL,
[Species_Community] [nvarchar](50) NOT NULL,
[Location_ID] [uniqueidentifier] NOT NULL,
[Site_ID] [uniqueidentifier] NULL,
[GIS_Location_ID] [nvarchar](50) NULL,
[Meta_MID] [nvarchar](50) NULL,
[X_Coord] [numeric](38, 8) NULL,
[Y_Coord] [numeric](38, 8) NULL,
[Coord_Units] [nvarchar](50) NULL,
[Coord_System] [nvarchar](50) NULL,
[UTM_Zone] [nvarchar](50) NULL,
[Accuracy_Notes] [nvarchar](255) NULL,
[Unit_Code] [nvarchar](12) NULL,
[Loc_Name] [nvarchar](100) NULL,
[Loc_Type] [nvarchar](25) NULL,
[Updated_Date] [nvarchar](50) NULL,
[Loc_Notes] [ntext] NULL,
[Datum] [nvarchar](5) NULL,
[Watershed] [nvarchar](50) NULL,
[StreamName] [nvarchar](50) NULL,
[NHDReachCode] [nvarchar](14) NULL,
[TOPO_NAME] [nvarchar](50) NULL,
[Trail] [nvarchar](100) NULL,
[Road] [nvarchar](50) NULL,
[Elevation] [numeric](38, 8) NULL,
[LAT] [numeric](38, 8) NULL,
[LON] [numeric](38, 8) NULL,
[txt_add] [nvarchar](100) NULL,
[Population_ID] [uniqueidentifier] NULL,
[Year_] [nvarchar](4) NULL,
[SHAPE] [int] NULL,
CONSTRAINT [PK_TBL_LOCATIONS] PRIMARY KEY NONCLUSTERED
(
[Location_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[TBL_LOCATIONS] WITH CHECK ADD CONSTRAINT [FK_TBL_LOCATIONS_TBL_POPULATION] FOREIGN KEY([Population_ID])
REFERENCES [dbo].[tbl_Population] ([Population_ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[TBL_LOCATIONS] CHECK CONSTRAINT [FK_TBL_LOCATIONS_TBL_POPULATION]
GO
ALTER TABLE [dbo].[TBL_LOCATIONS] ADD CONSTRAINT [DF_TBL_LOCATIONS_Location_ID] DEFAULT (newid()) FOR [Location_ID]
GO
June 30, 2011 at 2:43 pm
I think this is along the lines of what you're looking for. Please let us know:
CREATE TRIGGER dbo.TBL_LOCATIONS_after_insert ON dbo.TBL_LOCATIONS
AFTER INSERT
AS
BEGIN
INSERT INTO dbo.tbl_Events
(
Event_ID,
Location_ID
)
SELECT NEWID() AS Event_ID, Location_ID
FROM inserted ;
END
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 5, 2011 at 2:05 pm
how would that change to handle multirow inserts? Thanks
July 5, 2011 at 2:37 pm
No further replies here please. OP started a new thread with that question.
Direct replies to http://www.sqlservercentral.com/Forums/Topic1136799-391-1.aspx
Please in future keep it to one thread for a question. Thanks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply