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.
| Author |
Topic |
|
tpcolson
Starting Member
5 Posts |
 Posted - 2011-06-29 : 21:29:14
|
| I 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. |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-06-30 : 19:08:18
|
| I have the following schema:People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it. Please read enough on RDBMS so you will not use GUIDs as keys. Please stop putting that “tbl-” prefix on table names. Please learn about data types and their sizes. You don't even know that PRIMARY KEYs are non-null by definition. The idea that an event is BOTH an entity and an attribute of a location is absurd. The event and the location have a relationship. CREATE TABLE Eventsevent_id CHAR(15) NOT NULL PRIMARY KEY,observation_txt VARCHAR (500));CREATE TABLE Locationslocation_htm CHAR(18) NOT NULL PRIMARY KEY observation_txt VARCHAR (500)); CREATE TABLE Event_Calendar(event_id CHAR(15) NOT NULL REFERENCES Events(event_id) ON DELETE CASCADE ON UPDATE CASCADE, location_htm CHAR(18) NOT NULL REFERENCES Locations(location_htm) event_start_date DATE NOT NULL, event_end_date DATE NOT NULL, CHECK (event_start_date <= event_end_date), PRIMARY KEY (event_id, location_htm));>> Locations is the parent table with a relationship to events through Location ID.<<Parent table? That was the Network DB term; we have referenced and referencing tables in RDBMS. And a table never has both entities and relations in them; the table is one or the other, never both. >> Location_ID is set to be NewID() on create in Locations, <<Gee, that is a total waste of time. You cannot verify it. Use longitude/latitude or HTM (I like this better).>> .. and Event_ID is set to be NewID() on create in events. <<That is also a waste of time. Don't you have an encoding for the events? Event names? Anything that makes sense to a human being? I know that those unreadable GUIDs look just like the pointer chains in your old 1970's network DB, but you have to un-learn that mindset. >> The table, relationship, and PK format is non-changeable due to organizational policy governing replication. <<You are simply dead. None of my clients like to hear this, but it is true. >> 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 [sic: rows are not records – more network DB mindset] is created, it gets a location_id of 8170daed-92c8-47f1-98ca-147800329686, and the trigger creates a new event record [sic] also with a location_ID of 8170daed-92c8-47f1-98ca-147800329686 and an event_ID of cfed8fe8-b5be-4f78-b366-008672e39637. <<Yep, you are dead. Procedural trigger code will not scale or port, you are wasting huge amount of disk and have no way to verify anything in. No data integrity, no performance, no portability. After 25+ years of fixing SQL disasters, I can kludge something. But it will fail over time. After 25+ years of fixing SQL disasters, I also know when it is time t5o update the resume and start looking for another job. Sorry.--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-07-05 : 13:20:01
|
it will be likeCREATE TRIGGER EventInsertON [dbo].[tbl_Locations]AFTER INSERTASBEGININSERT INTO [dbo].[tbl_Events]([Location_ID],[Observation])SELECT Location_ID,<your value for Observation here>FROM INSERTEDEND ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-07-05 : 13:20:38
|
| By the way i didnt understand why you're having Event_ID in location table too. Will this be later populated by an update.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
insert trigger for related table?