problem with new,old variables

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

problem with new,old variables

Author

Topic

lakshmireddym
Starting Member

6 Posts

Posted - 2006-07-04 : 01:18:44

CREATE TRIGGER TGR_ENTITY_TYPE_ON_MOD
BEFORE UPDATE
ON ENTITY_TYPE
FOR EACH ROW
SET NEW.DATE_MODIFIED=NOW(),
NEW.DATE_ADDED=OLD.DATE_ADDED,
NEW.IS_INDEXED=FALSE;
Here in the table entity_type i have two columns date_added,date_modified...
how should i give their data types..(since two timestamp columns are not allowed)i want date_modified column to be modified whenever i update a row..How can i write the above trigger in sqlserver2000 format.i dont know how to use old,new variables in sqlserver.pls help me as early as possible.
Thanks in advance,
MLR

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-04 : 02:23:49

"since two timestamp columns are not allowed"
Are you using timestamp datatype ? Can you use datatime datatype instead ? timestamp datatype is not what it sounds like. It is actually a row version. Refer to BOL on more info on timestamp.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-04 : 02:25:47

quote:
CREATE TRIGGER TGR_ENTITY_TYPE_ON_MOD
BEFORE UPDATE
ON ENTITY_TYPE
FOR EACH ROW
SET NEW.DATE_MODIFIED=NOW(),
NEW.DATE_ADDED=OLD.DATE_ADDED,
NEW.IS_INDEXED=FALSE;

Is this T-SQL on MS SQL Server ?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-04 : 03:51:11

you don't need a trigger...

just add a default value as getdate() for the fields when you create the table

create table entity_type(
date_added datetime default getdate(),
date_modified datetime default getdate(),
is_indexed varchar(10) default 'false',
.
.
.
)

--------------------
keeping it simple...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-04 : 03:52:41

for the update, it is best to just one more field to update in your update statement than create a trigger just for this purpose

--------------------
keeping it simple...

lakshmireddym
Starting Member

6 Posts

Posted - 2006-07-04 : 23:54:05

If i use "datetime" datatype it doesnot accept the updated value.so, i am using "timestamp" datatype for another column.Actually What i should do is,Whenever i insert a record "date_added" column should get that present time.Whenever i update a record,"date_modified" column should get the updated time.To accept this updated time i am using timestamp datatype.i should write a trigger as whenever i update , "date_modified" column should get the updated value.But i dont know how to write in MSSQLServer.
Thanks for reply,Please help me for this also.
MLR

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-05 : 00:16:53

" If i use "datetime" datatype it doesnot accept the updated value."
What do you mean by this ?

Actually What i should do is,Whenever i insert a record "date_added" column should get that present time
Yes. Correct. If you define the data_added with default getdate(), you can omit the column in the insert statement if not you have to pass in getdate() to the date_added column

Whenever i update a record,"date_modified" column should get the updated time. i should write a trigger as whenever i update
You can use a update trigger to do this. Or just include the date_modified column in your update statement and set value to getdate()

Sample update trigger


create trigger tu_table 
    on  table 
    for update 
as
begin
    update t
        set date_modified = getdate()
        from inserted i inner join table t
        on   i.pk = t.pk
end

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-05 : 02:42:08

like what I posted

anyways, I believe your datetime problem may lie in the unrecognizable datetime format you are passing to your update?

check the valid datetime values and formats in books online, for faster search, search for 'convert'

HTH

--------------------
keeping it simple...

lakshmireddym
Starting Member

6 Posts

Posted - 2006-07-05 : 05:22:39

Yes,It is MS Sql.I have to write it in MS Sqlserver

lakshmireddym
Starting Member

6 Posts

Posted - 2006-07-05 : 05:39:29

create trigger tu_table
on table
for update
as
begin
update t
set date_modified = getdate()
from inserted i inner join table t
on i.pk = t.pk
end
What is "pk" here?I dont know.
Thanks in advance,
MLR

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-05 : 07:54:48

" Yes,It is MS Sql.I have to write it in MS Sqlserver"
I ask because the code that you posted in your 1st post does not looks like T-SQL.

pk is primary key column. You should inner join the inserted table with your table using the primary key column.

lakshmireddym
Starting Member

6 Posts

Posted - 2006-07-06 : 01:44:37

Thank u very much.I got my problem solved.And one more,can u please send any good pdf links to learn sqlserver.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-06 : 02:01:34

quote:
Originally posted by lakshmireddym

Thank u very much.I got my problem solved.And one more,can u please send any good pdf links to learn sqlserver.

http://www.w3schools.com/sql/default.asp
http://www.sql-tutorial.net/

X002548
Not Just a Number

15586 Posts

Posted - 2006-07-06 : 09:27:58

There is a reason it doesn't look like T-SQL

because it's not.

It looks like DB2


CREATE TRIGGER ASSESS_I                                  
       AFTER INSERT ON ASSESSMENT                        
       REFERENCING NEW AS N                              
       FOR EACH ROW MODE DB2SQL                          
       BEGIN ATOMIC                                      
           INSERT INTO ASSESS_AUDIT                      
            (EMPL_ID, UPD_DT, UPD_BY, ASSESSMENT_YEAR,   
             OCCURANCES, TRIGGER_NAME)                   
        VALUES                                           
            (N.EMPL_ID, CURRENT DATE, N.ADD_BY,          
             N.ASSESSMENT_YEAR, 1, 'ASSESS_I');          
       END#

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources