writing access data macros as SQL triggers

I have several instances in my database where the contents of one field are directly related to input into another field.  Previously, when I was just working in Access, I had addressed this using a macro to “setvalue” of the dependent field.  Now that I’ve upsized to SQL server, those macros won’t run and I can’t figure out any way to set the client end of my Access project to a trusted status so that those macros will run on the client side.

From what I’ve read these actions can/should be accomplished on the server end with the use of triggers, but the trigger I wrote doesn’t do anything even though the SSMS says the trigger was successfully created.

Here is an example of what I want to do:  I have 2 tables:  “Project_Info” (ID field: “PID”) and “Site_Info”  (Fields:  PID – Joined to PID of parent table Project_Info, Site_num – input by person, Site_ID – want to automatically be “PID dash Site_num”)

Two triggers that I want:

1.       I want a new PID in Project_Info to automatically be entered when a new record is inserted such that the new PID = Max(PID)+1

2.       I want the field Site_ID to be automatically filled with the concatenation PID + ‘-‘ + Site_num

Here’s the code I tried for the first one:

CREATE TRIGGER [dbo].[Create_PID]

ON  [dbo].[Project_Info]

AFTER INSERT

NOT FOR REPLICATION

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for trigger here

DECLARE @PID INT

SELECT  @PID=0

SELECT  @PID=MAX(PID)+1 FROM Project_Info;

UPDATE Project_Info

SET PID=@PID

END

I also tried:

CREATE TRIGGER [dbo].[Create_PID]

ON  [dbo].[Project_Info]

AFTER INSERT

NOT FOR REPLICATION

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for trigger here

UPDATE Project_Info

SET PID='MAX(PID)+1'

WHERE PID IS NULL

END

For the Site_ID I tried:

CREATE TRIGGER [dbo].[UpdateSiteID]

ON  [dbo].[Site_Info]

AFTER UPDATE

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for trigger here

UPDATE Site_Info

SET Site_ID=PID+'-'+Site_num

END

For all three of these I got the message “Command created successfully” after I hit execute, but then when I insert a new record in the database, nothing happens.  I know my front end (Access Project) is talking to the  tables SQL successfully because I can modify, add, delete data in Access and the changes show up when I look at the tables in SQL Server Management Studio.