Please help with a Trigger

Question

Please help with a Trigger

abdel_mesbah

SSC Enthusiast

Points: 132
More actions
September 2, 2013 at 8:53 am

#277635

Hi There,
I have just joined this great forum.
I have a question regarding Triggers pls
I have a table dbo.Levels and I need to track a start and end date of each level? I have 3 levels
Level 1, Level 2 and level 3? and the level is pull or I need it to match the actual level from another column within a table called dbo.maincase
any help would be much appreciated
Thank you
Abdel

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

There is a wee bit too much information missing in your post. I don't even see where the trigger would come into play.

As a start can you post the CREATE TABLE statement for your table and sample data, so we can get a little better understanding?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

abdel_mesbah SSC Enthusiast Points: 132 More actions · Answer 2

Hi there,

thank you for the answer and please find the create a table

Create TABLE Levels(

ID INT,

[Level] NVARCHAR(255),

DateCreated DATETIME DEFAULT GetDate(),

DateUpdated DATETIME DEFAULT GetDate()

)

GO

CREATE TRIGGER Upd ON Maincase

AFTER UPDATE ,INSERT

AS

UPDATE Levels

SET DateUpdated = GetDate()

FROM Levels INNER JOIN

deleted ON Levels.ID = deleted.ID

Go

I want to store the value of a level and the date when it started and if that level changes I want to store the date of when it changed again

Thanks Again

Abdel

venkat.ecengg 68608 SSC Journeyman Points: 84 More actions · Answer 3

Can you please provide the table schema of table maincase?

abdel_mesbah SSC Enthusiast Points: 132 More actions · Answer 4

idintno410 0 no

victimliaisonofficernvarcharno510 yes

recordidintno410 0 yes

victimsurnamenvarcharno450 yes

victimforenamenvarcharno450 yes

victimparentnvarcharno450 yes

victimdobdatetimeno8 yes

victimgendernvarcharno450 yes

victimethnicitynvarcharno450 yes

victimaddressnvarcharno450 yes

victimpostalcodenvarcharno2000 yes

victimtelephonenvarcharno450 yes

addvictimsurnamenvarcharno100 yes

addvictimforenamenvarcharno100 yes

addvictimparentnvarcharno100 yes

addvictimdobnvarcharno100 yes

addvictimgendernvarcharno100 yes

addvictimethnicitynvarcharno100 yes

addvictimaddressnvarcharno100 yes

addvictimtelephonenvarcharno100 yes

addvictimpostalcodenvarcharno100 yes

prisonnamenvarcharno450 yes

prisonnumbernvarcharno450 yes

offendersurnamenvarcharno450 yes

offenderforenamenvarcharno450 yes

offendercrnnvarcharno450 yes

offenderdobnvarcharno450 yes

offensesnvarcharno450 yes

sentensedatedatetimeno8 yes

offenderpostalcodenvarcharno2000 yes

courtnvarcharno450 yes

sentensenvarcharno450 yes

remandnvarcharno450 yes

dateofsentensenvarcharno450 yes

prisonlocationnvarcharno450 yes

prisonnumber1nvarcharno450 yes

hdcdatetimeno8 yes

arddatetimeno8 yes

leddatetimeno8 yes

seddatetimeno8 yes

hospitalordernvarcharno450 yes

referralnvarcharno450 yes

addprisonnumbernvarcharno100 yes

addoffendersurnamenvarcharno100 yes

addoffenderforenamenvarcharno100 yes

addoffendercrnnvarcharno100 yes

addoffenderdobnvarcharno100 yes

addoffensesnvarcharno100 yes

addsentensedatenvarcharno100 yes

addcourtnvarcharno100 yes

addsentensenvarcharno100 yes

addremandnvarcharno100 yes

addprisonnumber1nvarcharno100 yes

addhdcnvarcharno100 yes

addardnvarcharno100 yes

addlednvarcharno100 yes

addsednvarcharno100 yes

addhospitalordernvarcharno100 yes

addreferralnvarcharno100 yes

adddateofsentensenvarcharno100 yes

addprisonlocationnvarcharno100 yes

submitteddatedatetimeno8 yes

linkedtonvarcharno450 yes

casenonvarcharno450 yes

notesnvarcharno510 yes

locationnvarcharno450 yes

telephonenonvarcharno450 yes

victimliasionofficernvarcharno450 yes

statsdatedatetimeno8 yes

reviewdatedatetimeno8 yes

contactvcrnvarcharno450 yes

statusnvarcharno450 yes

victimknownvcnvarcharno450 yes

offenderknownvcnvarcharno450 yes

blockintno410 0 yes

addedbynvarcharno510 yes

initialletterdatedatetimeno8 yes

offendermanagernvarcharno450 yes

completedstatusintno410 0 yes

outofareareferralnvarcharno20 yes

stats_datenvarcharno510 yes

familymembernvarcharno10 yes

addfamilymembernvarcharno510 yes

conditionintno410 0 yes

levelnvarcharno510 yes

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Also some sample data for the tables would be useful.

Edit: Not a list of the columns in the table. The table's definition, the way you gave the Levels table.

The trigger as written is not going to work properly, the deleted table will always be empty for an insert. For an insert, update trigger you you probably rather want the inserted table

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

venkat.ecengg 68608 SSC Journeyman Points: 84 More actions · Answer 6

From your written I understand that you want to audit whenever a new level is added to the maincase table, if its a new level then you want to make an entry to it, if not any changes to the existing level to record the modified date?

If i'm right in this case, then you might want to check the logical tables (inserted & deleted) row count. If inserted table has more than 1 record and deleted has no records then make a new entry to Levels table. If both the table has more than one record then update the date time based on the logical table level field.

Hope it helps!:-)

abdel_mesbah SSC Enthusiast Points: 132 More actions · Answer 7

thats correct, I need kind of when 1 level is set to capture a date and if that level changes I need to record say when that level has changed again and insert some kind of 2nd row with the new level again and start date, which can be same as end date from teh prior row if I make sense?

Thank you

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 8

Kind of? One thing is for sure. If you don't know what you want, chances that we will know are slim.

In any case, please post the CREATE TABLE statement for a simplified version of Maincase, one that only has this level column and the primary key. Add to this sample data for Maincase and Level. Explain what should happen when a certain operation occurs on Maincase.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

abdel_mesbah SSC Enthusiast Points: 132 More actions · Answer 9

Hi there,

thank you for the reply and didnt mean the kind of as I dont know what I want 🙂

thats what I am capturing at the moment

Maincase

ID Level firstname surname

001 Level01 Jo Bloggs

if i change the level on main case above to level02 from level01 I need to record the date like below

ID Level datestarted date ended

001 Level01 01/01/2013 31/03/2013

001 level2 31/03/2013 a default date if the level has not changed as yet

Much Appreciate your input

Abdel

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 10

When I said INSERT statements, I did mean INSERT statements. That would have given you a tested solution. This is an untested solution to what I think (i.e. guess) what you want:

CREATE TRIGGER Upd ON Maincase

AFTER UPDATE, INSERT, DELETE AS

INSERT Levels (ID, Level)

SELECT ID, Level FROM inserted

UPDATE Levels

SET DateUpdated = GetDate()

FROM Levels

JOIN deleted ON Levels.ID = deleted.ID

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 11

abdel_mesbah (9/2/2013)
Hi there,
thank you for the reply and didnt mean the kind of as I dont know what I want 🙂
thats what I am capturing at the moment
Maincase
ID Level firstname surname
001 Level01 Jo Bloggs
if i change the level on main case above to level02 from level01 I need to record the date like below
ID Level datestarted date ended
001 Level01 01/01/2013 31/03/2013
001 level2 31/03/2013 a default date if the level has not changed as yet
Much Appreciate your input
Abdel

Abdel... take a look at the article at the first "Helpful Link" in my signature line below for how to post to get better and quicker responses. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

abdel_mesbah SSC Enthusiast Points: 132 More actions · Answer 12

abdel_mesbah

SSC Enthusiast

Points: 132

September 3, 2013 at 3:06 am

#1646919

Thank you jeff

abdel_mesbah SSC Enthusiast Points: 132 More actions · Answer 13

Hi Erland,

thank you for the query, it did work good for me and the only issue I need to fix now is

ID Level DateCreated DateUpdated

11577Higher Risk Level 2013-09-03 14:34:35.9272013-09-03 14:37:03.050

11577Information Level 2013-09-03 14:35:11.7972013-09-03 14:37:03.050

11577Representation Level 2013-09-03 14:37:03.0502013-09-03 14:37:03.050

How can I stop the dateUpdated to update for all the rows?

Many Thanks

Abdel

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 14

If I get this right, you should get it working by adding the condition

AND Levels.Level = deleted.Level

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]