asked on

SQL Triggers

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

DBAduck - Ben Miller

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.

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ste5an

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.

aj85

ASKER

Hello Mark,

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

Regards,
aj85