Access 2007 with SQL SERVER 2008 R2. In design phase. I am developing triggers for all tables and once I have them running successfully, I will move them to ModelRight for SQL Server so when I generate the database design it auto-creates all of these generic triggers for all tables.
Client requirement: All tables will contain the following fields 1) Creation Date, 2) Created By, 3) Modification Date, and 4) Modified By. These do NOT write to an Audit table. They exist only in the actual record created.
This is the first time I have ever written a trigger. When a new record is inserted it should date stamp the CreatedDT field and enter the user name CreatedBY. Once entered the Creation Date and CreatedBY fields never change. (The next trigger will have Modified Date and Modified By which will change every time the record is updated but I have to get this one working first.) My thinking is that both pieces of data should be inserted by one trigger since they both should be inserted after Inserting a new record.
I am finding most articles, videos, books to be fragmented in content, or demonstrating triggers with far more complicated processes then what I am trying to achieve. As a result they are not helpful to the beginner.
I finished the code below. I open up the dbo.Property table and insert a record. I cannot commit the change to see if the Trigger works. I get the error below. If I chop this down and only run the Create Date trigger I can get it to work. But I can't get it to work together.
My code is generating an error:
I don't understand the error or what is wrong with my code. I don't have anything clear to follow so I may have multiple errors or issues.
Remember I am a beginner on SQL SERVER and this is my first trigger.Code:CREATE TRIGGER DBO.TR_CreateDate On DBO.Property AFTER INSERT AS BEGIN SET NOCOUNT ON; Insert into Property(CreatedDT) values(GETDATE()) END GO CREATE TRIGGER DBO.TR_CreateName On DBO.Property AFTER INSERT AS BEGIN SET NOCOUNT ON; Insert into Property(CreatedBy) values(SUSER_SNAME()) END GO
- Can you include two actions in one trigger?
- Should they be separated as I have them?
- I am not sure SUSER_SNAME is correct or that the syntax is correct.
- If I execute the above trigger, before I try it on the table, it says that the Query executed successfully.
- Can one table have more than one trigger? Kind of the same question as 1.
- Should I write separate triggers for each action or combine them together with logic in between?
- Can anyone point me to clear examples of simple triggers like I am trying to create?
Phred