Solved

SQL Triggers

Posted on 2014-10-11
4
151 Views
Last Modified: 2014-10-13
Hello All,

I have two triggers that I am trying to execute.  The first script attached is supposed to get the Juilian date and calculate the individuals age based upon their date of birth.  There may be an easier way to get the correct age calculation so if there is, please advise.  The next script seems to have an issue with my inserting an image into the table, although the column has been declared with the correct datatype.  So I am not sure why this error message is being returned in MS SQL Server.  Thanks in advance for any assistance you can offer.

Regards,
aj85
Trigger-Errors.txt
0
Comment
Question by:aj85
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 40375326
These triggers are not used correctly.  Usually triggers operate on the inserted and deleted tables to detect or affect other things.  The first trigger is breaking because you have a SET [AgeDays] equal to a subquery that returns more than one column.  You cannot do that in an update statement.  The SET can only set a column equal to 1 value and there is not a where clause, so if it worked, it would set the value for ALL rows in the table.

The second trigger does nothing, and the error is because of the varbinary(max) column.  These columns cannot be manipulated or retrieved from the inserted tables.  So the error is related to that.  But the statement at the bottom will do nothing to the table as you only SET @TStamp which is a variable not even part of the table you have the trigger on.

I am not sure I see the goal of these triggers.

If you can help us understand what you intend and why you chose triggers, that may help us give you the right guidance.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 40375518
Well, the UPDATE set agedays gets the error because you are selecting three different fields (count the number of calculate AS results)

For the second one, check the setting of @content - in fact comment that out for the moment and see if that makes the difference.

Might help to see the full triggers to see the ramifications and help with correct usage.

Be ware that some datatypes don't always convert the way you want implicitly.  So, might also help if we can see the create script for the Photos table.

And (by the way) you should be using @@DBTS to get the last used value of a TIMESTAMP column, it will be updated with the update, so if checking update(tsamp) and tsamp is a timestamp column, then there is a basic logic problem (maybe)... see : http://msdn.microsoft.com/en-us/library/ms187366(v=sql.90).aspx
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40376665
I have two triggers that I am trying to execute.  The first script attached is supposed to get the Juilian date and calculate the individuals age based upon their date of birth.

This is wrong. When you read your table in three weeks, the calculated age is off by three weeks. This simply wrong.
Age calculations are always done "on-the-fly". In a view, a stored procedure or in an adhoc query. Or a calculated column.

The second trigger is logically not necessary. Cause you erase the value set by a user. I guess you want a modified date column to indicate when the *row* has changed. This is slightly different:

CREATE TRIGGER trgUpdatePhotoTStamp ON [Photos]
    FOR UPDATE
AS
    SET NOCOUNT ON;
    UPDATE  P
    SET     TStamp = CONVERT(VARBINARY, GETDATE(), 101)
    FROM    Photos P
            INNER JOIN INSERTED I ON I.primaryKeyColumns = P.primaryKeyColumns;
GO

Open in new window


But why on earth are you using a VARBINARY column?

Are you mixing the TIMESTAMP data type with the common usage of the word timestamp?
The TIMESTAMP data type in T-SQL is a binary update counter, not a date/time value.
0
 

Author Comment

by:aj85
ID: 40376957
Hello Mark,

Thanks for the link it was very helpful in assisting to solve my issue.

Regards,
aj85
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Rebooting Witness SQL Server 2 25
sql server cross db update 2 20
Sql server Error message 3 16
Running Total Using new MS SQL Function 21 50
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question