July 26, 2012 at 1:07 pm
I have to create an update and insert trigger for a table based on the value of a column on the same table i.e. i have to add a date to a column when there is a value of date is null and statusID is 1 or active.I tried to create a trigger as below but it didnot work as expected
Create trigger [AddDAte3] on [dbo].[Status]
for Insert,Update
as
Begin
update dbo.Status
set Date = GetDATE()
where StatusID in (select StatusID from inserted where Date = 'NULL' and StatusId = '1');
what am i doing wrong
the Status table looks as below
UserID
user
name
Status
date
July 26, 2012 at 1:12 pm
SQLTestUser (7/26/2012)
I have to create an update and insert trigger for a table based on the value of a column on the same table i.e. i have to add a date to a column when there is a value of date is null and statusID is 1 or active.I tried to create a trigger as below but it didnot work as expectedCreate trigger [AddDAte3] on [dbo].[Status]
for Insert,Update
as
Begin
update dbo.Status
set Date = GetDATE()
where StatusID in (select StatusID from inserted where Date = 'NULL' and StatusId = '1');
what am i doing wrong
the Status table looks as below
UserID
user
name
Status
date
Is the column Date a datetime? A datetime will never equal the string literal 'NULL'. To check for NULL use IS NULL.
...
where Date IS NULL
...
I would bet that StatusID is an int? You should use an int literal 1 instead of the string '1'. Otherwise sql has to do an implicit conversion first.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 26, 2012 at 1:36 pm
July 26, 2012 at 1:37 pm
Jayanth_Kurup (7/26/2012)
u cud try a computed column with a function call also.
Computed column won't work for a value that's based on GetDate(). Value of the column would change every time you query it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 26, 2012 at 1:39 pm
The trigger is probably failing because of the NULL bit already mentioned, but I want to suggest that you move the code out of a trigger and into a stored proc used to update the table. Makes it easier to document, debug, and refactor, for later devs who want to work with that table. Is that possible?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 26, 2012 at 1:43 pm
July 26, 2012 at 2:33 pm
I changed the trigger but the trigger is still now working , any suggestions ??
CReate Trigger [AddDate] on [dbo].[Status]
for Update,Insert
as
update dbo.Status
set DATE = GetDATE()
where StatusID in (select StautsID from inserted where DAte IS NULL and StatusID = 1);
July 26, 2012 at 2:40 pm
SQLTestUser (7/26/2012)
I changed the trigger but the trigger is still now working , any suggestions ??CReate Trigger [AddDate] on [dbo].[Status]
for Update,Insert
as
update dbo.Status
set DATE = GetDATE()
where StatusID in (select StautsID from inserted where DAte IS NULL and StatusID = 1);
Please post the CREATE TABLE statement for the table dbo.Status.
July 27, 2012 at 6:34 am
CELKO (7/26/2012)
>> I have to create an update and insert trigger for a table based on the value of a column on the same table I.e. I have to add a date to a column when there is a value of date is null and statusID is 1 or active. I tried to create a trigger as below but it did not work as expected <<This does not make sense to me on several levels.
DATE is a reserved word in SQL. It is also what ISO calls an attribute property; that means a valid data model would have “<something>_date” for the data element name. This same international standard applied to the improperly named Status table, email, name and user.
The same standards also tell us that “status_id” is invalid; it is like string of adjectives without a noun. If this was supposed to be a “user_status”, then it is an attribute of a user and should be in the Users table.
We now have CURRENT_TIMESTAMP so there is no need to use the old 1970's getdate(). We do not use BIT flags in SQL; that was the 1950s and assembly language programming.
I will guess that the number of user status values is small. Can you use a CHECK (user_status IN ()) constraint on the column. A status is a state of being, so it has a duration. Here is a skeleton for this kind of history table. There are some other things you can do to avoid gaps in the time line, etc.
CREATE TABLE Users
(user_id CHAR(1) NOT NULL PRIMARY KEY,
user_email VARCHAR (255) NOT NULL,
user_name VARCHAR (255) NOT NULL,
user_status CHAR (6) NOT NULL
CHECK (user_status IN ('active', ..),
start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATE, --- null is current status
CHECK (start_date <= end_date),
PRIMARY KEY (user_id, start_date));
Instead of procedural trigger code, we can write good SQL declarative code in a stored procedure that adds history data to each user's data. Also, most people these days use the email address and a password for a user identifier. People do not forget them and they are easy to test.
There are a significant number of flaws in this:
First, e-mail is a completely junk identifier for a human being. There are several reasons for this, not the least of which is that any given person can have more than one, can have different ones at different times, and (largely because of GMail) e-mail addresses aren't necessarily static even within their domain. (GMail allows variable placement of a period in the name portion of the e-address. Thus, for example, "joecelko@gmail.com" = "joe.celko@gmail.com" = "j.oecelko@gmail.com".) Thus, as a "key" for people, it's junk.
Second, e-mail as an identifier/username, unless it's for an entirely internal application, has security problems. It tends to create the re-use of username/password pairs, called "password proliferation" or "identity proliferation", which is one of the most common security holes in existence. It also means that anyone who knows another person's e-address knows their username, which also reduces security significantly.
Beyond the abuse of e-mail and very poor security model:
"DATE" is not a reserved word in SQL. That's just plain false. It's also a meaningless assertion, because, even if it were a reserved word, it can still be used as a column/object name, simply by escaping it. You can build a table named "FROM" with a column named "SELECT" and another column named "WHERE", and end up with some amusing query statements, if you want to.
While it's true that CURRENT_TIMESTAMP means you don't "have to use ... getdate()", it also doesn't mean you can't/shouldn't use it. It's just an option. I understand that you have a problem with it, but that's just an opinion on your part. Asserting personal opinions as facts detracts from the usefulness of your post.
I also seriously doubt that the non-compliance with a bunch of ISO standards on the naming actually renders you incapable of understanding what's being done with the table and code. Saying so (which you do) is either evidence that you, Joe, are amazingly stupid, or is (much more likely) simply dishonest. Don't claim "a column named Date makes it so I can't tell what the column is for" (which is what you did assert) unless it actually does go beyond your comprehension. If you want your precious ISO standards complied with, present a compelling and honest reason for them. Some actual advantage, not a false "This does not make sense to me on several levels" assertion.
Your date dimension is also flawed. By having an end-date column, you create the possibility that a status can end before or after the next status begins. It's also a violation of the valid uses of NULL per ANSI & ISO standards (where NULL = unknown value, and "no end date" is a known value which simply can't be represented in ISO/ANSI SQL because of flaws in the standards).
So, in summary, please ignore Joe's diatribe. It is (a) flawed, (b) internally inconsistent, and (c) dishonest.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply