Trigger problem! - SQL Server Forums

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

Trigger problem!

Author

Topic

romzilla
Starting Member

6 Posts

Posted - 2007-11-15 : 02:55:28

Hi
I am new to SQL server (MS SQL Server 2005 Standart Edition) so maybe someone could help me with trigger problem. I have just gone through some material about triggers, but still some issues should be cleared. Let me try do describe the situation
I have table profile, colums f_condition(integer), f_glabat_lidz(integer) and f_lietas_dat(date) in it. f_lietas_dat is filled by user in application but field f_glabat_lidz needs to be calculated automatically(
f_glabat_lidz = year of f_lietas_dat + 5 if f_condition = 5
f_glabat_lidz = year of f_lietas_dat + 10 if f_condition = 10
for example). This should be done on every insert or update. I created this example trigger, this is the idea, but it does not work. Maybe someone could help to solve this problem? It is very urgent! Thank You for help!

CREATE TRIGGER profile_trg
ON profile
FOR INSERT, UPDATE
AS
BEGIN
INSERT profile (f_glabat_lidz)
SELECT YEAR(f_lietas_dat)+1
FROM inserted
END

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 03:02:13

when you say it "doesn't work", do you find that it adds another row instead of updating your id field? that would be because you've made it an insert when it should be an update

Em

romzilla
Starting Member

6 Posts

Posted - 2007-11-15 : 03:09:56

Doesn't work means that no data is inserted. The application shows error in sql command. I also edited my topic with a condition, please reread it. Condition is used to know what number to sum.
P.S. going to be offline now, so sorry for no answering

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 03:19:52

what i meant is this...


create table test (myid int, adate datetime)
go

CREATE TRIGGER profile_trg
ON test
FOR INSERT, UPDATE
AS
BEGIN
INSERT test (myid)
SELECT YEAR(adate)+1
FROM inserted
END
go

insert into test (adate) values ('20070101')

select * from test

myid    adate
NULL	2007-01-01 00:00:00.000
2008	NULL

you need to update that row, not insert a new one. the other conditions you've put in don't change that logic

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 03:32:19

more like this...


create table test2 (pk int, condition int, myid int, adate datetime)
go

CREATE TRIGGER profile_trg2
ON test2
FOR INSERT, UPDATE
AS
BEGIN
update test2 
set myid = YEAR(i.adate)+ i.condition
from test2 t join inserted i on i.pk = t.pk
END
go

insert into test2 (pk,condition,adate) values (1,5,'20070101')

select * from test2

PK      condition  myid   adate
1	5	   2012	  2007-01-01 00:00:00.000

romzilla
Starting Member

6 Posts

Posted - 2007-11-21 : 10:47:10

Thank You very much for the help! It was just in time! To many work to think about anything. The final trigger that is working, as it should is like

SYSTEM_ID (integer) – primary key
F_IZN_GADS (integer) – value that should be calculated
F_GLABAT_TERM (integer) – condition value
F_LIT_DAT (date) – date to for calculations

CREATE TRIGGER profile_trg
ON test
FOR INSERT, UPDATE
AS
BEGIN
update test
set F_IZN_GADS =
case
when (i.F_GLABAT_TERM = 3 or i.F_GLABAT_TERM = 5 or i.F_GLABAT_TERM = 10) then YEAR(i.F_LIT_DAT)+ i.F_GLABAT_TERM
else null
end
from test t join inserted i on i.SYSTEM_ID = t.SYSTEM_ID
END
go

Thank you for the help again!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources