October 21, 2008 at 7:48 pm
Hi,
I need to create a trigger but am only new to the language. Could anyone suggest the programming for the following:
I have 2 records (by SiteID) per link (by LinkID). The LinkID is connected to the SiteID's via another table. There is definitely always 2 records (sites) per link.
If Date1 is changed for Record1 (in the sites table), then I need to check if (Date1 is not null) or (Field1 is True) for Record2 (in the sites table). If either is the case, then Update FinalDate1 for the relevant Link (in the link table).
If Date1 is changed to Null for Record1 (in the sites table), then I need to set FinalDate1 to Null for the relevant Link (in the link table).
There are extra checks that are required but if someone could help me with the syntax on these first couple I should be able to make the rest work.
Thanks in advance.
October 21, 2008 at 11:16 pm
jwellington (10/21/2008)
Hi,I need to create a trigger but am only new to the language. Could anyone suggest the programming for the following:
I have 2 records (by SiteID) per link (by LinkID). The LinkID is connected to the SiteID's via another table. There is definitely always 2 records (sites) per link.
If Date1 is changed for Record1 (in the sites table), then I need to check if (Date1 is not null) or (Field1 is True) for Record2 (in the sites table). If either is the case, then Update FinalDate1 for the relevant Link (in the link table).
If Date1 is changed to Null for Record1 (in the sites table), then I need to set FinalDate1 to Null for the relevant Link (in the link table).
There are extra checks that are required but if someone could help me with the syntax on these first couple I should be able to make the rest work.
Thanks in advance.
You required to create the update trigger on the table. Use the following link for more
http://msdn.microsoft.com/en-us/library/aa258254(SQL.80).aspx
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 21, 2008 at 11:26 pm
Here are commands that you're going to need.
Use UPDATE() to check if the column you're monitoring is the one that was updated by the DML.
Use the inserted and deleted logical tables to check the values of the column before and after update.
Use TRANSACTION processing to maintain data integrity.
Happy coding!
-- CK
October 22, 2008 at 12:02 am
Hi,
Thanks for your replies. I can create the trigger and use the update statements, but am having difficulty figuring out the code to use to check fields on an alternate record with the same LinkID and different SiteID.
If they were all part of the same record (same siteID), I would just use the field name,
but in this instance, the other information (fields) to check are in a different record (different SiteID) in the same table, but with the same LinkID from another table.
Any ideas on method of coding to check the other record would be greatly appreciated.
Thanks
October 22, 2008 at 12:39 am
Hi,
Thanks, I am able tp link the tables using Joins, but am still trying to figure out how to write the code to check the fields on the other record with the same LinkID.
Hope that makes sense.
October 22, 2008 at 1:03 am
Hi
So u need to check for another record in the same table i.e. sites table rt?
If thats the case you can use IF EXISTS or IF NOT EXISTS Clause inside your trigger and do the necessary checking and if the checking is satisfied/not satisfied do your updation.
BTW Are you using stored procs to do the initial update?
check out BOL for the syntax on IF EXISTS or IF NOT EXISTS .
"Keep Trying"
October 22, 2008 at 2:06 am
Hi,
I am not checking if the record exists. There are definitely 2 records per LinkID at all times. When a field is updated in one of the records, I need to check the values of other fields in the other record (with the same LinkID).
No I am not using Stored Procedures, just Triggers.
Thanks
October 22, 2008 at 7:21 am
At this point it would help to have some DDL and sample data for these tables. We are only going to be able to throw theory / keywords at you until we can see your actual environment and put something together to show you what we're talking about. Please see the link in my signature for how to post this information.
October 22, 2008 at 7:29 pm
Hi,
OK I will do my best. Lets see how it goes. I have reduced the number of fields in each table to keep everything simple, so the tables are not actually as small as they appear.
I have labelled LinkID and SiteID so you know where the relationships are, and detailed them in the following text.
----------Create Tables---------------
----------Links Table----------
CREATE TABLE [dbo].[tblLinks](
[LinkID] [int] IDENTITY(1,1) NOT NULL,
[LinkNumber] [nvarchar](20) NOT NULL,
[LinkName] [nvarchar](100) NULL,
[Timestamp] [timestamp] NULL,
CONSTRAINT [PK_tblLinks] PRIMARY KEY CLUSTERED
(
[LinkID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
----------LinksDetails Table---------
CREATE TABLE [dbo].[tblLinksDetails](
[LinkID] [int] NOT NULL,
[LinkStage1ForecastDate] [datetime] NULL,
[LinkStage1ActualDate] [datetime] NULL,
[LinkStage2ForecastDate] [datetime] NULL,
[LinkStage2ActualDate] [datetime] NULL,
[Timestamp] [timestamp] NULL,
CONSTRAINT [PK_tblLinksDetails] PRIMARY KEY CLUSTERED
(
[LinkID] 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]
------------LinksList Table------------- to join LinkID to SiteID
CREATE TABLE [dbo].[tblLinksList](
[LinkID] [int] NOT NULL,
[SiteID] [int] NOT NULL,
[Timestamp] [timestamp] NULL,
CONSTRAINT [PK_tblLinksList] PRIMARY KEY CLUSTERED
(
[LinkID] ASC,
[SiteID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
----------------Sites Table------------------
CREATE TABLE [dbo].[tblSites](
[SiteID] [int] IDENTITY(1,1) NOT NULL,
[SiteNumber] [nvarchar](20) NOT NULL,
[SiteName] [nvarchar](100) NULL,
[SiteType] [nvarchar](10) NULL,
[Timestamp] [timestamp] NULL,
CONSTRAINT [PK_tblSites] PRIMARY KEY CLUSTERED
(
[SiteID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--------------SitesDates Table------------------
CREATE TABLE [dbo].[tblSitesDates](
[SiteID] [int] NOT NULL,
[SiteStage1ForecastDate] [datetime] NULL,
[SiteStage1ActualDate] [datetime] NULL,
[SiteStage2ForecastDate] [datetime] NULL,
[SiteStage2ActualDate] [datetime] NULL,
[upsize_ts] [timestamp] NULL,
CONSTRAINT [PK_tblSitesDates] PRIMARY KEY CLUSTERED
(
[SiteID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
----------SitesInformation Table--------------
CREATE TABLE [dbo].[tblSitesInformation](
[SiteID] [int] NOT NULL,
[SiteStatus] [nvarchar](50) NOT NULL,
[Timestamp] [timestamp] NULL,
CONSTRAINT [PK_tblSitesInformation] PRIMARY KEY CLUSTERED
(
[SiteID] 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]
---------------Relationships---------------------
Links Table to LinksDetail Table by LinkID
Links Table to LinkList Table by LinkID
LinksList Table to Sites Table by SiteID
Sites Table to SitesDates Table by SiteID
Sites Table to SitesInformation Table by SiteID
-------------------Insert Test Data (hope this works)---------
INSERT INTO tblLinks
(LinkID, LinkNumber, LinkName)
SELECT '1',’123_456’,’A to B’, UNION ALL
INSERT INTO tblLinksDetails
(LinkID)
SELECT '1' UNION ALL
INSERT INTO tblLinksList
(LinkID, SiteID)
SELECT '1',’1’ UNION ALL
SELECT '1',’2’ UNION ALL
INSERT INTO tblSites
(SiteID, SiteNumber, SiteName)
SELECT '1',’123’,’A’ UNION ALL
SELECT '2',’456’,’B’ UNION ALL
INSERT INTO tblSitesDates
(SiteID)
SELECT '1' UNION ALL
SELECT '2' UNION ALL
INSERT INTO tblSitesInformation
(SiteID, SiteStatus)
SELECT '1',’No Change’ UNION ALL
SELECT '2',’Upgrade’ UNION ALL
--------------Outline of proposed Trigger---------------------------
If Update(Stage1ForecastDate) then
If SiteStatus of other SiteID with same LinkID = ‘No Change’ then
LinkStage1ForecastDate = Stage1ForecastDate entered
else
If not Isnull(Stage1ForecastDate of other SiteID with same LinkID) then
LinkStage1ForecastDate = The greater of the two SiteStage1ForecastDates
End if
End if
End if
---------------------------
I should be able to figure out the rest if someone can show me how to do this.
Thanks for any help. I hope the information is easy to deal with.
As this is my first post of code, any further tips are appreciated.
October 25, 2008 at 10:27 am
jwellington,
Thank you for posting the information requested. I realized today that this one seemed to have kinda slipped through the cracks. So I copied your table definition, pasted it into my test database and clicked run. Yeah. Not good. The reason we ask for sample DDL / Data is so that we can easily duplicate your environment and spend our time actually helping you with your problem. The code above is Jam packed with errors. I figured I would take a shot at correcting them since this post was a few days old.
I fixed the invalid Syntax on the Primary Keys.
I replaced all your Word smart quotes (which are actually tilde's) with actual single quotes. (This one is a bit harder to spot, but would have been revealed if you had tried to run your own test code)
I removed all the extra Union alls in your INSERT/SELECT statements
Removed all of the comments in the middle of your script that weren't --'d out
Removed all the extra commas in select lists
Removed the 'TextImage on' with no corresponding fields
Got an error trying to directly insert identity fields into a table.
At this point I stopped, as I wasn't going to sit there and track all of those down. If this is still an issue and you haven't found a solution, please post some correct code, and someone here might be able to help you. The reason this one likely has not been answered yet is that nobody is willing to wade through 50 different syntax errors just to replicate your environment. Also, please post more than 1 row on the main tables so that we can be sure it is performing adequately. (Getting something to run on only one row proves almost nothing)
October 27, 2008 at 8:50 pm
Hi Seth,
Thanks for your response and the time that you spent reviewing my code and trying to make it work.
I didn't expect to go into this much detail but understand why you are requesting it.
Due to my limited knowledge on programming on SQL Server with SQL, I was really looking for some tips on how one might refer to a field in another record with a different ID, but it seems that either I wasn't explaining myself well, or it was a bigger question than I had thought (more likely).
I don't think I have completely got my head around posting code yet, so will leave this for now.
So thanks again. I really appreciate everyone's input on this forum. It has been so helpful in the past in finding quick solutions.
October 27, 2008 at 11:01 pm
Just to make sure you understand, I wasn't trying to chastise you for your errors, and I appreciate you giving it a shot. The only reason I listed out all the things I did was so you were aware of some of the things that were wrong that you could possibly improve upon the next time you posted code. Sometimes typed text comes across a lot harsher than spoken words, and in re-reading my post, I can see that this might have be one of those cases.
I'm sorry we weren't able to work out a solution to this, but if you feel like taking another shot at the explanation / posting, I'm sure someone here can help you with your issue.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply