Solved

HOW DO I CREATE STORED PROCEDURE / TRIGGER IN SQLSERVER 2005

Posted on 2007-12-06
5
229 Views
Last Modified: 2010-04-21
My questions is the following. I have an MS access program that allows users to enter detailed event logs. However, I  want to create a trigger in MSSQL SERVER 2005 and be able to update the table T_LOG based on the event type added at T_LogEvents.  

So for example. If a created the following record in T_LOGEVENTS...
-EventID: 05513546 (this is an autonumber)
-EventType: DOCUMENT
-Dn_number: EP071105001  
-EventDate: 12/12/07 11:53
-LogUser: epicazo
-LogAction: NOTIFIED
-ToFrom:  Paula Jones

I want to the trigger in SQL2005 to acknowledge the add in T_LOGEVENTS, and then update the record in T_LOG, lets say SELECT   DN_NUMBER FROM  T_Log WHERE (DN_NUMBER = N'EP071105001'), and if column DBO.T_LOGEVENTS.LogAction ="NOTIFIED", then the trigger will update the T_LOG record with the following ... dbo.T_LOG.enotify=dbo.T_LogEvents.EventDate, AND T_Log.eLastEventBy=dbo.T_LogEvents.LogUser

Below are my tables.  There is a relation of one (t_log) to many (t_logevents)
<<<<PARENT>>>>TABLE: dbo.t_log
...COLUMNS...
-DN_NUMBER...(PK) nvarchar(20)
-inituser...nvarchar(15)
-initdate...datetime
-eNotify...datetime
-eLastEvent...datetime
-eLastEventBy...NVARCHAR(15)

<<<<CHILD>>>>TABLE: dbo.t_LogEvents
...COLUMNS...
-EventId...(PK) numeric(18,0)... identity increment 1
-EventType...nvarchar(20)
-Dn_number...nvarchar(20)
-EventDate...datetime
-LogUser...nvarchar(15)
-LogAction...nvarchar(25)
-ToFrom...nvarchar(30)


Obviously the syntax is not correct, but I am so green in trigger, never dealt with them, so I'll need a step by step on how to create this trigger.

THANKS
0
Comment
Question by:epicazo
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:Yveau
Comment Utility
create trigger:
http://msdn2.microsoft.com/en-us/library/ms189799.aspx

create stored procedure:
http://msdn2.microsoft.com/en-us/library/ms187926.aspx

... and a trigger is just as a stored procedure. Only thing is that you can't directly run it, the RDBMS will ... One other thing, you have two 'virtual' tables in the triggers: Inserted (in insert and update trigger) and Deleted (in update and delete trigger) to help you deal with the records before the DML hit the table ...

Hope this helps ...
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
Comment Utility
create trigger trigger_T_logevents
on T_logevents
after insert
as
begin

update tl set
  enotify=inserted.EventDate,
  eLastEventBy=inserted.LogUser
from dbo.t_log tl
  inner join inserted on tl.DN_NUMBER = inserted.DN_NUMBER
where inserted.LogAction ='NOTIFIED'

end
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
Run the above code from Query Analyzer or SSMStudio
0
 

Author Comment

by:epicazo
Comment Utility
This is perfect THANK YOU so much.  Two (well three) additional questions -- if you don't mind:
Will all my triggers have the "update tl set" string?
Why name it TL and not anything else -- what exactly is it doing here

Also, could you recommend additional resources books to learn more about triggers and stored procedures?  I want to learn more of this stuff -- it's awesome!


0
 

Author Closing Comment

by:epicazo
Comment Utility
:)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now